Ravindra BagaleCourses & study guides Track your progress

Guides

SAMEPERIODLASTYEAR and Year-over-Year % in DAX

By , Power BI & Data Analytics trainer LinkedIn Published: Last updated:

SAMEPERIODLASTYEAR shifts the current date filter back one year; combine it with DIVIDE to build clear year-over-year percent measures — and name LY vs YTD-LY so nobody confuses the story.

Chala mitrano! “Are we better than last year?” is the board question. Why? Growth needs a parallel period, not vibes. How? We write Sales LY with SAMEPERIODLASTYEAR, then YoY % with DIVIDE, on FreshBasket (fictional) months. Stay precise with names.

Quick answer

Vertical YoY path:

  1. Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ).
  2. Matrix: Month + Total Sales + Sales LY.
  3. YoY % = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] ).
  4. Format YoY % as percentage.
  5. Handle missing LY with DIVIDE (blank/alternate).
  6. If you shift a YTD measure, call it Sales YTD LY — not plain LY.
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )

Why YoY, rows in / number out

Month This year Last year
Jan 100 90
Feb 40 50
Mar 80 70
  1. Why SAMEPERIODLASTYEAR: board wants March this year vs March last year.
  2. On March this year, [Total Sales] = 80.
  3. SAMEPERIODLASTYEAR shifts the date filter back one year. Sales LY = 70.
  4. YoY % = DIVIDE ( 80 - 70, 70 ) ≈ 14.3%.
  5. Feb story: this year 40, LY 50, YoY % is negative — that is honest.
  6. Mumbai focus: if a City slicer is on, both TY and LY respect Mumbai. City total this year might still be 140 for Jan+Feb while LY is whatever last year held.
SAMEPERIODLASTYEAR Shifts the date filter back one year for a like-for-like compare. This year SAMEPERIODLASTYEAR LY ly

SAMEPERIODLASTYEAR shifts the current date filter back one year for a like-for-like compare.

What do I need before this guide?

Before and after (look at the tables first)

Before YoY This year March 100; last year also listed.

Before

After YoY Last year 80; YoY change +20.

After

SAMEPERIODLASTYEAR

Sales LY =
CALCULATE (
    [Total Sales],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)
  1. Takes the dates in context and shifts them back one year.
  2. March 2026 compares with March 2025 when Month is on the axis.
  3. Needs the same solid Date table as YTD.

YoY percent

YoY percent YoY % = DIVIDE ( this − last, last ). TY LY DIVIDE ( TY−LY, LY )YoY % yoy

YoY % pattern: DIVIDE ( [Sales] - [Sales LY], [Sales LY] ) — then format as percent.

YoY % =
DIVIDE (
    [Total Sales] - [Sales LY],
    [Sales LY]
)
  1. (This year − last year) / last year.
  2. Format as %.
  3. Alternative form DIVIDE ( [Total Sales], [Sales LY] ) - 1 — pick one style and stay consistent.

Phone-bill analogy: this month’s bill vs the same month last year. That is SAMEPERIODLASTYEAR. The percent change is DIVIDE.

LY vs YTD LY (naming)

LY vs YTD LY Same period last year vs last-year YTD are different stories — name them clearly. SAMEPERIODLASTYEARparallel dates YTD then shift LYdifferent grain label

Name measures clearly: parallel period last year is not the same story as “YTD shifted to last year”.

  1. Parallel period last year ≠ “take YTD then shift”.
  2. Both are valid; titles must say which.
  3. Interview tip: define the grain before writing DAX.

Mistakes and calm fixes

Symptom Likely cause Fix
LY blank No prior-year data / bad dates Check Date table + data coverage
Huge YoY Wrong denominator Use Sales LY, not a random total
Confusing card Vague name “Growth” Call it YoY %
YTD mixed in Copied wrong measure Separate Sales LY vs Sales YTD LY

Ghabru naka 😅 — blank LY in year one of a business is normal.

Ravindra Bagale's Tip

Interview trap: “How do you calculate YoY?” Answer with both pieces: SAMEPERIODLASTYEAR for last year, DIVIDE for the percent — then mention the Date table. Got it?

Practice task

  1. Create Sales LY and YoY %.
  2. Month matrix with TY, LY, YoY %.
  3. Find a month with no LY; note DIVIDE behaviour.
  4. Optionally build Sales YTD LY with a clear name.
  5. Write one sentence defining YoY for your report consumers.

Learn it properly

Course lessons:

Related guides: Running totals · DIVIDE

Got it? SAMEPERIODLASTYEAR for LY; DIVIDE for YoY %. Name YTD-LY separately. Next: running totals. Let's continue.

Frequently asked questions

What does SAMEPERIODLASTYEAR do?

Returns a set of dates shifted one year back from the dates in the current filter context.

How do I get YoY %?

Compare this period to LY with DIVIDE ( (TY - LY), LY ) or DIVIDE ( TY, LY ) - 1 — be consistent.

LY vs previous month?

Different helpers exist for other shifts; this guide focuses on year-over-year.

Why is LY blank?

No data last year for those dates, broken Date relationship, or filter context with no parallel dates.

Can I combine with YTD?

Yes — patterns like CALCULATE ( [Sales YTD], SAMEPERIODLASTYEAR ( … ) ) exist; name them Sales YTD LY.

Course lesson?

Time intelligence in the DAX chapter.