Microsoft Certified: Power BI Data Analyst AssociateVisualize and analyze the dataEasy
A data analyst is creating a Power BI report to visualize sales data. The report needs to display the total sales for the current year and compare it to the total sales of the previous year. Additionally, the analyst wants to show the percentage change between these two values. Which DAX function is most appropriate for calculating the sales of the previous year?
- APARALLELPERIOD(Dates[Date], -1, YEAR)
- BCALCULATE(SUM(Sales[SalesAmount]), PREVIOUSYEAR(Dates[Date]))
- CDATEADD(Dates[Date], -1, YEAR)
- DSAMEPERIODLASTYEAR(Dates[Date])
Show answer & explanationAnswer & explanation
Correct answer: D. SAMEPERIODLASTYEAR(Dates[Date])
The SAMEPERIODLASTYEAR function is specifically designed to retrieve a set of dates that represent the same period as the given dates, but shifted back by one year. This makes it ideal for direct year-over-year comparisons.
Why the other options are wrong
- A. PARALLELPERIOD returns a set of dates in the same parallel period as the specified dates, with the given interval. While it can achieve similar results, SAMEPERIODLASTYEAR is more straightforward for a direct 'last year' scenario.
- B. PREVIOUSYEAR is not a standalone filter function like SAMEPERIODLASTYEAR; it's typically used within CALCULATE to modify filter context for a single previous year, but SAMEPERIODLASTYEAR offers a more concise and direct approach for the specified requirement.
- C. DATEADD shifts dates by a specified interval, but SAMEPERIODLASTYEAR is more direct for year-over-year comparisons.
SAMEPERIODLASTYEAR DAX Function
A DAX time intelligence function that returns a table that contains a column of dates representing the same period as the dates in the specified dates column, in the previous year.
- Used for year-over-year comparisons.
- Requires a date column as an argument.
- Returns a table of dates.
Memory trick: Time travel with DAX, period by period, last year's sales, clear as day.