The SAME PERIOD LAST YEAR function in DAX is mainly used for time intelligence in Power BI and Analysis Services. It’s designed to compare values from the same period in the previous year — for example, comparing sales this year to sales for the same month or quarter last year.
Technically, the function returns a set of dates from the previous year that correspond to the dates in the current filter context. Its syntax is very simple:
SAMEPERIODLASTYEAR()
Here, <dates> is usually a column from your date table, like 'Date'[Date].
Let’s take a practical example. Suppose you have a measure:
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
This measure calculates the total sales for the same period in the previous year.
So, if your report is filtered for March 2025, this function dynamically shifts the date range to March 2024 and returns that period’s sales value for comparison.
I’ve applied this in dashboards where management wanted year-over-year (YoY) analysis — for instance, comparing current month revenue with the same month last year, or tracking YoY growth trends for KPIs like profit or customer acquisition.
One challenge I faced was ensuring that the Date table was properly marked as a Date table and had a continuous range of dates — because SAMEPERIODLASTYEAR depends on having a valid, continuous date column. If there are missing dates or the relationship isn’t properly established with the fact table, the function might return blank or incorrect results.
Another limitation is that it works best for a single contiguous period like one month or one quarter. If you select multiple years in your filter context, it might not behave as expected.
As an alternative, when you need more flexible or custom comparisons — say comparing current quarter to same quarter last year regardless of missing dates — I often use DATEADD() or PARALLELPERIOD().
For example:
Sales LY = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, YEAR))
This gives more control, especially if your time periods aren’t exactly aligned or you’re working with non-standard fiscal calendars.
So in short, SAME PERIOD LAST YEAR is a powerful and straightforward function for YoY comparisons, but it relies heavily on a clean, well-structured date table and works best when your time filters are standard and continuous.