DAX for Financial Years
I use these two measures quite a bit. YTD Total Sales is straight forward and calculates in this case a sum of sales from the 1st of July to today. The trick is what to do when you want to compare the same period for the previous year. That is where a handy function called SAMEPERIODLASTYEAR comes very handy. Check out the functions below and give it a try.
Note: ‘Calendar’ is a Calendar Table, ‘Query1’ is a fact table
Measure for YTD
This measure calculates a sum for the financial year ending 30th of June
YTD Total Sales =
CALCULATE ( SUM ( Query1[Sales] ), DATESYTD ( 'Calendar'[Date], "06/30" ) )
CALCULATE ( SUM ( Query1[Sales] ), DATESYTD ( 'Calendar'[Date], "06/30" ) )
Measure for YTD for the previous year
Total LYTD =
VAR DataMaxDate =
CALCULATE ( MAX ( Query1[Date] ), ALL ( Query1 ) )
RETURN
CALCULATE (
[YTD Total Sales],
SAMEPERIODLASTYEAR ( DATESBETWEEN ( 'Calendar'[Date], BLANK (), DataMaxDate ) )
)
VAR DataMaxDate =
CALCULATE ( MAX ( Query1[Date] ), ALL ( Query1 ) )
RETURN
CALCULATE (
[YTD Total Sales],
SAMEPERIODLASTYEAR ( DATESBETWEEN ( 'Calendar'[Date], BLANK (), DataMaxDate ) )
)