Microsoft Certified: Power BI Data Analyst AssociateModel the dataEasy
A client is developing a Power BI model to analyze product sales performance over time. The model includes a 'Sales' table, a 'Products' table, and a 'Date' table. The client wants to calculate the total sales for the 'current' month (based on the latest date in the 'Date' table that has sales data) and compare it to the total sales of the previous month. Which DAX time intelligence function is best suited to retrieve the sales for the previous month in this scenario?
- APARALLELPERIOD
- BSAMEPERIODLASTYEAR
- CDATEADD
- DPREVIOUSMONTH
Show answer & explanationAnswer & explanation
Correct answer: D. PREVIOUSMONTH
PREVIOUSMONTH returns a table that contains all dates from the previous month, based on the first date in the current filter context. This directly addresses the need to get sales for the month immediately preceding the 'current' month.
Why the other options are wrong
- A. PARALLELPERIOD returns a set of dates in the same period as the specified dates, in a parallel period shifted by a number of intervals, which is more general than the specific 'previous month' requirement.
- B. SAMEPERIODLASTYEAR shifts the date context back by exactly one year, which is not what is required for the previous month.
- C. DATEADD allows adding or subtracting a specified number of intervals (days, months, years) to a set of dates, but PREVIOUSMONTH is more specific for whole months.
PREVIOUSMONTH Function
A DAX time intelligence function that returns a table containing all dates from the previous month, based on the first date in the current filter context.
- Part of Power BI's time intelligence functions.
- Used to compare current period data with the immediately preceding month.
- Requires a marked date table.
Memory trick: Time travel functions: Previous month, year, or custom period.