Microsoft Certified: Power BI Data Analyst AssociateModel the dataEasy
A data analyst is working on a Power BI model for sales forecasting. The model includes a 'Sales' table and a 'Date' table. The analyst needs to calculate the total sales for the same period as the current selection, but for the previous year. For example, if the current selection is Q1 2023, the measure should show sales for Q1 2022. Which DAX time intelligence function is most suitable for this requirement?
- ADATEADD
- BSAMEPERIODLASTYEAR
- CPREVIOUSYEAR
- DPARALLELPERIOD
Show answer & explanationAnswer & explanation
Correct answer: B. SAMEPERIODLASTYEAR
SAMEPERIODLASTYEAR is a specialized time intelligence function that returns a table that contains a column of dates that are shifted one year back in time from the dates in the specified dates column, preserving the period (e.g., day, month, quarter).
Why the other options are wrong
- A. DATEADD allows shifting by any interval (day, month, quarter, year), but SAMEPERIODLASTYEAR is more direct and optimized for the specific 'same period last year' scenario.
- C. PREVIOUSYEAR returns a table that contains all dates from the previous year, which would cover the entire previous year, not just the 'same period' as the current selection.
- D. 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 generic than the specific 'same period last year' requirement.
SAMEPERIODLASTYEAR Function
A DAX time intelligence function that returns a table containing dates that are shifted one year back from the dates in the current filter context, preserving the period.
- Compares current period with the equivalent period in the previous year.
- Automatically handles year-over-year comparisons.
- Requires a marked date table.
Memory trick: Same period, just last year.