Use of SAMEPERIODLASTYEAR (or similar DAX functions) together with filters and slicers
This article explains how to use the SAMEPERIODLASTYEAR function (or TOTALYTD, DATEADD, PARALLELPERIOD, PREVIOUSMONTH…) together with filters / slicers.
This can be more complicated than it seems.
Let´s demonstrate it on this table, showing dates, numbers and categories.
If the syntax is like this, then it somehow works. In visual we can see current values as well as previous year values:
- Lastyear = CALCULATE(
SUM(‘Table'[Number of something]),
SAMEPERIODLASTYEAR(‘Table'[Day]),
ALL())
But when you select some category (using filter, slicer etc.), there is something wrong:
So what?
Let´s start with a new table (dimension) creation, using the CALENDARAUTO function:
This should be connected with the original table, using relation.
Now change the syntax of measure. ALL function wouldn’t be used. SAMEPERIODLASTYEAR will use the date column from time dimension, which will be also used in visual.
- Last Year = CALCULATE(
SUM(‘Table'[Number of something]),
SAMEPERIODLASTYEAR(‘Time dimension'[Date]))
From now, it works perfectly – no matter how many filters is used.
This article explains how to use the SAMEPERIODLASTYEAR function (or TOTALYTD, DATEADD, PARALLELPERIOD, PREVIOUSMONTH...) together with filters / slicers.
This can be more complicated than it seems.
Let´s demonstrate it on this table, showing dates, numbers and categories.
If the syntax is like this, then it somehow works. In visual we can see current values as well as previous year values:
- Lastyear = CALCULATE(
SUM('Table'[Number of something]),
SAMEPERIODLASTYEAR('Table'[Day]),
ALL())
But when you select some category (using filter, slicer etc.), there is something wrong:
So what?
Let´s start with a new table (dimension) creation, using the CALENDARAUTO function:
This should be connected with the original table, using relation.
Now change the syntax of measure. ALL function wouldn't be used. SAMEPERIODLASTYEAR will use the date column from time dimension, which will be also used in visual.
- Last Year = CALCULATE(
SUM('Table'[Number of something]),
SAMEPERIODLASTYEAR('Time dimension'[Date]))
From now, it works perfectly - no matter how many filters is used.
It is recommended practice to use “calendar table” marked as “date table” when using Time Intelligence functions.
Clear and simple explanation. Thank you!