Shift dates exactly one year back for YoY metrics.
On this page
SAMEPERIODLASTYEAR shifts the active date filter context back exactly one calendar year while preserving the grain of the current period (day, month, quarter). It is the standard function for year-over-year (YoY) comparisons in time-intelligence measures.
It requires a contiguous Date table marked as a date table and an active relationship between your fact table and that Date table.
A simplified Sales fact table with a related Date column (OrderDate). Amounts are in USD:
Sample Data Table: Sales
| OrderID | OrderDate | Region | Amount |
|---|---|---|---|
| 201 | 2024-01-15 | North | 1200 |
| 202 | 2024-02-10 | South | 800 |
| 203 | 2024-03-05 | North | 1500 |
| 204 | 2025-01-20 | North | 1400 |
| 205 | 2025-02-12 | South | 950 |
| 206 | 2025-03-08 | West | 1100 |
Total Revenue = SUM(Sales[Amount])Revenue Last Year =
CALCULATE(
[Total Revenue],
SAMEPERIODLASTYEAR('Date'[Date])
)Revenue YoY % =
DIVIDE(
[Total Revenue] - [Revenue Last Year],
[Revenue Last Year]
)Place Date[Month] (or Month name) on rows with [Total Revenue], [Revenue Last Year], and [Revenue YoY %] in a matrix or table visual. Filter the report to year 2025:
Output Table Visual (2025 filter active)
| Month | Total Revenue | Revenue Last Year | Revenue YoY % |
|---|---|---|---|
| January | 1400 | 1200 | 16.7% |
| February | 950 | 800 | 18.8% |
| March | 1100 | 1500 | -26.7% |
| Total | 3450 | 3500 | -1.4% |
For each visible month in 2025, SAMEPERIODLASTYEAR replaces the current date context with the same calendar month one year earlier. January 2025 compares to January 2024 ($1400 vs $1200). March 2025 looks weaker because March 2024 had a larger North order ($1500). The function does not return a full prior-year total in one cell—it always mirrors the same period as the row or slicer grain.