Use of SAMEPERIODLASTYEAR (or similar DAX functions) together with filters and slicers
This article describes how to use SAMEPERIODLASTYEAR in a filtered / slicered table.
It is not that easy, since tables and charts with SAMEPERIODLASTYER normally crashes in this situation.
Let´s work with this example. There is a number of incidents for each day.
We can start with a new visual, containing current and last year number of incidents (using SAMEPERIODLASTYEAR). Now it works:
But when we use new column for filtering, the visual does not work.
So what to do?
We have to create a table with list of dates, connect it with original table, and work with it. Now step by step...
Table with dates
- Calendar = CALENDAR(DATE(2005;1;1);DATe(2020;12;31))
New calendar table has to be connected with original table, using Date as key. The relationship has to be defined as Many to One (or One to Many) and Cross filter directon as Single.
Measure creation and its use
Measure is created by SAMEPERIODLASTYEAR, whose argument is taken from calendar date table. This measure is normally used in visual, which is grouped by calendar table.
This approach is quite complicated. If you now something easier, please write it to comment.