SAMEPERIODLASTYEAR
Returns the same dates one year earlier, for use as a filter in CALCULATE.
SAMEPERIODLASTYEAR(<dates>)Returns: A table of dates.
What it does
SAMEPERIODLASTYEAR moves the visible dates back exactly one year. Put it inside CALCULATE with a measure, and the measure shows the prior year's figure for the same dates. Finance teams use it for year-over-year comparisons, for growth percentages and for prior year to date figures. Because it moves the visible dates, a partial year compares the same days, which is the fair comparison when the current year is not finished.
Syntax and arguments
- dates: the date column of the Calendar table. Required.
Example
A monthly revenue report shows the current year and the prior year side by side, with the growth rate. Each row is a month, so the prior-year figure is the same month last year.
Revenue PY = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Calendar'[Date]))
Revenue YoY % = DIVIDE([Total Revenue] - [Revenue PY], [Revenue PY])
In the October 2026 row, Revenue PY adds up October 2025. DIVIDE returns the growth rate and gives a blank rather than an error when the prior year has no value.
Example: a second, different use
Prior year to date for a June 30 fiscal year. The measure reuses a year to date total, and SAMEPERIODLASTYEAR moves the context back a year first.
Revenue YTD = TOTALYTD([Total Revenue], 'Calendar'[Date], "6/30")
Revenue YTD PY = CALCULATE([Revenue YTD], SAMEPERIODLASTYEAR('Calendar'[Date]))
In a November 2026 row, Revenue YTD PY covers July 1, 2025 through November 30, 2025. The same days of the prior fiscal year are compared.
Things to watch
- It needs contiguous dates in the Calendar. A missing day in the table can break the shift.
- It moves the visible dates, so a month row compares with the same month and a day row compares with the same day.
- In a matrix with quarters on rows and years on columns, the shift can fail. Put the year before the quarter on rows.
- DIVIDE avoids division errors when the prior year is blank, which is common for a new product or a new customer.
In business terms
SAMEPERIODLASTYEAR is the core of year-over-year reporting. It drives the growth rate on the board pack, the seasonality check in the budget, and the prior year comparison in the audit analytics. Use it with year to date measures to compare like with like.