You are enhancing a Power BI model that has DAX calculations. You need to create a DAX measure that returns the year-to-date total sales from the same date from the previous calendar year. How…

This question tests knowledge of DAX time intelligence functions needed to compute year-to-date sales through the equivalent date in the prior calendar year. The correct approach combines TOTALYTD (or DATESYTD inside CALCULATE) with SAMEPERIODLASTYEAR.

Implement and manage data models

Question

You are enhancing a Power BI model that has DAX calculations. You need to create a DAX measure that returns the year-to-date total sales from the same date from the previous calendar year. How should you complete the measure? To answer, select the appropriate options in the answer area.

Explanation

Approach. The correct measure wraps SAMEPERIODLASTYEAR around the date column inside a TOTALYTD or DATESYTD call, then aggregates sales within CALCULATE. For example: PriorYearYTD = TOTALYTD(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date])) - or equivalently CALCULATE(SUM(Sales[Amount]), DATESYTD(SAMEPERIODLASTYEAR('Date'[Date]))). SAMEPERIODLASTYEAR shifts the current filter context back exactly one year, and TOTALYTD/DATESYTD then constrains that shifted range to only dates from January 1 through the matching day, producing a true prior-year YTD figure. The optional third argument to TOTALYTD or DATESYTD can specify a fiscal year-end date (e.g., '3/31') if the calendar is not a standard Jan–Dec year.

Concept tested. DAX time intelligence - combining TOTALYTD (or CALCULATE + DATESYTD) with SAMEPERIODLASTYEAR to produce a year-to-date aggregate anchored to the equivalent date in the previous calendar year.

Reference. Microsoft Learn - DAX time intelligence functions: TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR (docs.microsoft.com/en-us/dax/)

Topics

#DAX#Time Intelligence#Measures#CALCULATE

Community Discussion

No community discussion yet for this question.

Full DP-500 Practice