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

Let´s create a new table. We can use New Table and this DAX syntax, generating list of days between two 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.

1 Comment

  1. It is recommended practice to use “calendar table” marked as “date table” when using Time Intelligence functions.

Leave a Reply

Your email address will not be published.


clear formPost comment