Day 9 — Time intelligence — comparing to last year

Every management report asks the same question: how does this compare with last year?

It all depends on the Calendar table

Time intelligence functions work by manipulating a date table. Without a proper Calendar — one row per day, no gaps — none of them work reliably.

731 days in the Calendar, but sales happened on only 48 of them. That gap is exactly why the Calendar table has to be the one driving the visuals.

SAMEPERIODLASTYEAR

=CALCULATE(measure, SAMEPERIODLASTYEAR(Calendar[Date]))

It shifts whatever dates are currently in context back by one year, then runs the measure over those.

2025 shows 2024’s figure beside it. 2024 shows blank, correctly — there is no 2023 in this model, and inventing a zero there would be worse than showing nothing.

Year-on-year growth

2025 grew 36.2% on 2024. Note that DIVIDE handles the 2024 row gracefully — the denominator is blank, so the result is blank rather than an error breaking the visual.

Ignore the Total row here It reads 136.2%, which is nonsense. With no year in context, "Sales" means both years while SAMEPERIODLASTYEAR still resolves to 2024 — so it compares two years against one. Growth percentages are meaningful per period and meaningless in aggregate. In a real report you would turn the total row off for that column, and this is precisely the kind of number that reaches a board pack if nobody checks.

DATEADD — shift by anything

=CALCULATE(measure, DATEADD(Calendar[Date], -1, YEAR))

Same answer as SAMEPERIODLASTYEAR here, but DATEADD is more flexible: change YEAR to MONTH for a month-on-month comparison, or the number to -2 to reach further back.

Year to date

With a whole year in context, the year-to-date figure equals the year total. It becomes interesting broken down by month, where each row accumulates everything from January up to that point.

Financial years Indian financial years run April to March. TOTALYTD takes an optional year-end date for exactly this: TOTALYTD(SUM(Sales[Amount]), Calendar[Date], "31/3"). This playground handles the calendar year only — the real function handles both.

The pattern to remember

Every time-intelligence measure has the same shape CALCULATE( the measure you already have, a function that changes the dates )

Write and test the plain measure first. Only then wrap it. If Total Sales is wrong, Total Sales Last Year will be wrong in a way that is far harder to spot.

Comparing regions across years

Filtering the year explicitly with CALCULATE is often clearer than time intelligence when you want two fixed years side by side. Time intelligence earns its keep when the period has to follow a slicer.

Try these yourself

Write a measure for 2024 sales using CALCULATE.
Write a last-year measure for the Qty column.
Explain why the 2024 row of Last Year is blank.
Explain why the Total row of Growth % should be ignored.
Break Sales and Last Year down by Product[Category].

← PreviousDay 8 — Filter context and row context Next →Day 10 — Iterators, ranking and top-N