Archive

Archive for August, 2012

DAX – SAMEPERIODLASTYEAR and DATESYTD

August 26, 2012 2 comments

One of my favourite financial systems business scenarios is to track the financial metrics at given period with different previous periods.
Here we can see an interesting metrics scenario and simplest way of solving using DAX functions and considering AdventureWorks Tabular model for this demo.
Requirement:
Track Sales, Sales YTD, Prev Year Sales and Prev Year Sales YTD and need to implement in the model rather than DAX query at report to maintain the flexibility of reporting tool.
Measures:

SalesYTD:=TOTALYTD(SUM('Internet Sales'[Sales Amount]),'Date'[Date])

Prev Year Sales:=CALCULATE([Internet Total Sales],SAMEPERIODLASTYEAR(  'Date'[Date]  ))

Prev Year Sales YTD:=CALCULATE([Internet Total Sales],SAMEPERIODLASTYEAR( DATESYTD( 'Date'[Date] ) ))


Results:

Points to consider:
1. Please note that Prev Year Sales and Prev Year Sales YTD are different
2. DAX calculation context used DATESYTD
3. Consider one of the similar scenarios discussed at msdn forum with possible DAX context issues and solutions using CALCULATE, SAMEPERIODLASTYEAR and DATESYTD.

Categories: DAX Tags: