Microsoft Certified: Power BI Data Analyst AssociateVisualize and analyze the dataMedium
A financial analyst is creating a Power BI report to track the company's monthly expenses. The report needs to display the current month's expenses alongside the expenses from the previous month and the same month in the prior year for comparison. Which DAX function should the analyst use to retrieve the previous month's expenses?
- APARALLELPERIOD(Dates[Date], -1, MONTH)
- BSAMEPERIODLASTYEAR(Dates[Date])
- CCALCULATE(SUM(Expenses[Amount]), PREVIOUSMONTH(Dates[Date]))
- DDATEADD(Dates[Date], -1, YEAR)
Show answer & explanationAnswer & explanation
Correct answer: C. CALCULATE(SUM(Expenses[Amount]), PREVIOUSMONTH(Dates[Date]))
The PREVIOUSMONTH function is specifically designed to shift the context to the previous month, making it ideal for comparing the current month's data with the preceding month's data within a CALCULATE statement. The other options are for different time-intelligence scenarios.
Why the other options are wrong
- A. PARALLELPERIOD with '-1, MONTH' shifts the entire period, which might be too broad if only the previous month's total is needed. PREVIOUSMONTH is more direct for a single prior month.
- B. SAMEPERIODLASTYEAR shifts the context to the same month in the previous year, not the immediately preceding month.
- D. DATEADD with 'YEAR' shifts the context by a full year, not by one month.
PREVIOUSMONTH DAX Function
A time intelligence function that returns a table that contains all dates from the previous month, based on the first date in the current selection.
- Used within CALCULATE to shift context to the prior month.
- Requires a date column as an argument.
- Useful for month-over-month comparisons.
Memory trick: Time DAX functions are like a calendar, jumping to past dates with care.