Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data modeler is building a Power BI model for a financial institution. The model contains a 'Transactions' table with a 'TransactionDate' column and a 'TransactionAmount' column. The model also has a 'CurrencyExchangeRates' table that stores daily exchange rates. The modeler needs to calculate the total transaction amount in USD for all transactions that occurred in the 'Current Month'. The 'Current Month' is determined by the latest 'TransactionDate' in the 'Transactions' table. Which DAX expression should the modeler use to achieve this?
- ACALCULATE(SUM(Transactions[TransactionAmount]), FILTER(ALL(Transactions[TransactionDate]), MONTH(Transactions[TransactionDate]) = MONTH(MAX(Transactions[TransactionDate])) && YEAR(Transactions[TransactionDate]) = YEAR(MAX(Transactions[TransactionDate]))))
- BCALCULATE(SUM(Transactions[TransactionAmount]), DATESINPERIOD(Transactions[TransactionDate], MAX(Transactions[TransactionDate]), -1, MONTH))
- CCALCULATE(SUM(Transactions[TransactionAmount]), DATESBETWEEN(Transactions[TransactionDate], STARTOFMONTH(MAX(Transactions[TransactionDate])), ENDOFMONTH(MAX(Transactions[TransactionDate]))))
- DCALCULATE(TOTALMTD(SUM(Transactions[TransactionAmount]), Transactions[TransactionDate]))
Show answer & explanationAnswer & explanation
Correct answer: C. CALCULATE(SUM(Transactions[TransactionAmount]), DATESBETWEEN(Transactions[TransactionDate], STARTOFMONTH(MAX(Transactions[TransactionDate])), ENDOFMONTH(MAX(Transactions[TransactionDate]))))
Option D correctly uses DATESBETWEEN and STARTOFMONTH/ENDOFMONTH to filter the transactions to the current month based on the latest transaction date. This approach is robust and accurately defines the month boundary.
Why the other options are wrong
- A. This expression tries to manually filter by month and year, which can be less efficient and error-prone, especially with calendar edge cases.
- B. DATESINPERIOD is used for a specified number of intervals from an end date, but -1 MONTH would include the entire previous month, not just the current one.
- D. TOTALMTD aggregates over the month-to-date, which is not what the question asks for (total for the entire current month).
Dynamic Month Filtering
Filtering data to show values for the current month, where 'current' is determined by the latest date in the dataset, often using DAX date intelligence functions.
- Uses MAX() to find the latest date.
- STARTOFMONTH() and ENDOFMONTH() define the month boundaries.
- DATESBETWEEN() applies the date filter.
Memory trick: Maximum date sets the stage, then boundaries define the month's age.