Module 6: Time intelligence · Lesson 6.2 · about 16 min
Same period last year and growth
Compare any period with the same period a year earlier, work out growth, and handle new stores and missing history correctly.
Module 6 · about 16 minutes · read first: 6.1. You’ll be able to write last-year and growth measures that are right for months, quarters, years and new stores.
1. Why this matters
“Are we up on last year?” is the first question in every retail meeting. Growth measures that break on new stores or show Infinity are the first thing a sharp manager notices.
2. Last year and growth
Write Sales LY: the same period last year, with SAMEPERIODLASTYEAR. Then Growth % = (Total Sales − Sales LY) ÷ Sales LY.
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Growth % = DIVIDE([Total Sales] - [Sales LY], [Sales LY]) Trace a 2025 month: the visual’s dates are replaced by the same dates one year earlier, and the sum runs on those. The 2024 rows have no last year in the data, so Sales LY is blank and so is growth. Northbay grew about 29 % in 2025: busier stores, a 4 % price rise and a new store.
3. New stores
A slicer keeps 2025. Split by Store: Forks Market had no 2024 sales. Make Growth % blank when there are no sales last year (it already is with DIVIDE: check why) and add Change = Total Sales − Sales LY that is blank for new stores too.
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Growth % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])
Change = IF(ISBLANK([Sales LY]), BLANK(), [Total Sales] - [Sales LY]) DIVIDE already makes Forks Market’s growth blank. Change needs an explicit test. Many retailers go further and report like-for-like (same-store) growth: only stores open for the whole of both periods, so new stores don’t flatter the number. You’d add a filter such as Store[OpenDate] <= DATE(2024, 1, 1).
4. DATEADD and friends
| Function | Gives |
|---|---|
SAMEPERIODLASTYEAR('Date'[Date]) |
the visible dates, one year earlier |
DATEADD('Date'[Date], -1, MONTH) |
one month earlier (any number and unit: DAY, MONTH, QUARTER, YEAR) |
PREVIOUSMONTH('Date'[Date]) |
the whole month before the first visible date |
PARALLELPERIOD('Date'[Date], -1, YEAR) |
the whole previous year (full periods, not shifted days) |
So month-on-month: Sales LM = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH)).
Principle
Principle: growth compares two filter contexts that differ only in time:
Try it: March 2025 sales 21,792, March 2024 sales 15,461. Growth = (21,792 − 15,461) / 15,461 ≈ 41 %.
5. Worked example
The December 2025 board slide. December sales 30,031 against 22,313 a year earlier: +34.6 %. Year to date 262,471 against 203,438: +29 %. Both numbers come from the same two measures with different filter contexts (month row or YTD measure inside the shift, Lesson 6.4).
6. Practice
- Write
Sales LMandMoM %(month on month). - Why does Growth % on the total row (no date filter) show a strange number?
- Write
Sales LYwith DATEADD instead of SAMEPERIODLASTYEAR.
Answers
Sales LM = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH)),MoM % = DIVIDE([Total Sales] - [Sales LM], [Sales LM]).- The total covers both years; “last year” of both years is just 2024, so it compares two years with one. Hide growth on totals that span several years, or show it only per year.
CALCULATE([Total Sales], DATEADD('Date'[Date], -1, YEAR)).
Check yourself
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.