SAMEPERIODLASTYEAR (Year-over-Year)
Power BI · Advanced DAX & Time Intelligence
Theory
SAMEPERIODLASTYEAR(dates) returns the dates in the current filter context shifted back by one year. Used inside CALCULATE, it gives the value of a measure for the same period last year: if the current cell is March 2024, it returns the dates of March 2023. From there, year-over-year growth is a simple DIVIDE of the difference by last year's value.
With the sample history, January 2024 sales are ₹1,200 versus ₹1,000 in January 2023, a 20.0% increase; February is ₹2,300 versus ₹1,800 (27.8%); March is ₹2,200 versus ₹2,500, a fall of 12.0%. It works at any granularity: on a year row it compares the full year, on a quarter row the quarter. The date table must contain both years, so a calendar built only for 2024 would return blank for last year. SAMEPERIODLASTYEAR is equivalent to DATEADD(dates, -1, YEAR).
Why we use it
Year-over-year comparison removes the effect of seasonality and is the standard way businesses judge growth, so almost every sales report needs it.
When to use it
Use it for same-period comparisons with last year at any level (month, quarter, year). For shifts other than one year, such as previous month, use DATEADD; for full previous-year totals regardless of the period, use PREVIOUSYEAR or PARALLELPERIOD.
How it works
- Visual cell: Mar 2024
- Read the current dates (1-31 Mar 2024)
- SAMEPERIODLASTYEAR shifts them to 1-31 Mar 2023
- CALCULATE evaluates Total Sales on the shifted dates
- Does last year have data? Yes: Compute YoY % with DIVIDE. No: Show blank.
- Current vs last year side by side
Syntax
SAMEPERIODLASTYEAR(<dates>)
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])Example
Sales LY =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR('Date'[Date])
)
YoY % =
VAR Cur = [Total Sales]
VAR Prev = [Sales LY]
RETURN DIVIDE(Cur - Prev, Prev)
// Mar 2024: (2,200 - 2,500) / 2,500 = -12.0%Key points
- Shifts the current dates back exactly one year
- Use inside CALCULATE to get last year's value
- The date table must cover both years
- Works at month, quarter and year level
- Equivalent to DATEADD(dates, -1, YEAR)