Microsoft Certified: Power BI Data Analyst AssociateVisualize and analyze the dataMedium
A data analyst is designing a Power BI report for a manufacturing company. The report needs to compare the current month's production volume with the production volume of the same month in the previous year. This comparison should be prominently displayed in a card visual. Which DAX function is most appropriate for calculating the previous year's production volume?
- ACALCULATE
- BDATEADD
- CSAMEPERIODLASTYEAR
- DTOTALYTD
Show answer & explanationAnswer & explanation
Correct answer: C. SAMEPERIODLASTYEAR
The SAMEPERIODLASTYEAR DAX function is specifically designed to return a set of dates that are in the same period as the selected dates, but shifted back by one year. This directly addresses the need to compare current month's data with the same month last year.
Why the other options are wrong
- A. CALCULATE changes the filter context of an expression but isn't specific to time intelligence for previous year periods on its own.
- B. DATEADD shifts a set of dates by a specified interval, which could be used but SAMEPERIODLASTYEAR is more direct for this specific scenario.
- D. TOTALYTD calculates the year-to-date total, not the same period last year.
SAMEPERIODLASTYEAR DAX Function
The SAMEPERIODLASTYEAR DAX function returns a table that contains a column of dates shifted back one year in time, but in the same period as the dates in the specified 'dates' column. It's crucial for year-over-year comparisons.
- Time intelligence function.
- Returns a single column of dates.
- Used within CALCULATE to apply the date filter context.
- Requires a contiguous date table for optimal performance.
Memory trick: Last year's same period, a DAX function clear, SAMEPERIODLASTYEAR brings the data near.