Module 6: Time intelligence · Lesson 6.4 · about 18 min
Pace against last year and targets
Compare this year to date with last year to date, bring in a monthly targets table with TREATAS, and build the "are we on track?" measures every retail dashboard needs.
Module 6 · about 18 minutes · read first: 6.1 and 6.2. You’ll be able to build pace and target measures, and connect a table at a different grain.
1. Why this matters
The questions behind most retail dashboards are “are we ahead of last year?” and “are we on target?”. These are the measures in the capstone, and the kind of thing a BI developer gets asked for in week one.
2. Pace
Pace: this year to date against last year to date. Write YTD LY by shifting the YTD dates back a year, and Pace %.
Press Run to see the result.
Move the whole period back a year with SAMEPERIODLASTYEAR('Date'[Date]); the YTD measure then runs inside the shifted dates.
Total Sales = SUM(Sales[Amount])
YTD = TOTALYTD([Total Sales], 'Date'[Date])
YTD LY = CALCULATE([YTD], SAMEPERIODLASTYEAR('Date'[Date]))
Pace % = DIVIDE([YTD] - [YTD LY], [YTD LY]) On the March 2025 row, YTD covers 1 January to 31 March 2025. YTD LY shifts those dates back a year and runs the YTD measure in them: 1 January to 31 March 2024. Pace % compares the two. By December, Northbay is about 29 % ahead.
Pace is a better headline than a single month’s growth, because one bad month (a snowstorm, a holiday moving) doesn’t swing it.
3. Targets at a different grain
Targets is monthly per store, with a YearMonth text column and no relationship to the daily Date table. Rather than a messy many-to-many relationship, move the filter inside the measure:
Targets has a YearMonth column but no relationship to Date. Write Target that sums Targets and moves the Date filter across with TREATAS(VALUES('Date'[YearMonth]), Targets[YearMonth]). Then vs Target %.
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
Target = CALCULATE(SUM(Targets[Target]), TREATAS(VALUES('Date'[YearMonth]), Targets[YearMonth]))
vs Target % = DIVIDE([Total Sales] - [Target], [Target]) VALUES('Date'[YearMonth]) lists the months visible in the cell; TREATAS applies them as a filter on Targets[YearMonth]. Store filters reach Targets through its real relationship to Store. Northbay beat its 2025 targets, which were set at last year plus 6 %, because sales grew much faster.
Other ways to handle different grains: add a YearMonth dimension that relates to both tables, or relate Targets to the Date table on the first day of each month. TREATAS keeps the model simple and is a favourite interview answer.
Principle
Principle: compare like with like. Pace compares two periods of the same length at the same point in the year; target comparisons only make sense at the target’s own grain (month and store) or coarser (quarter, region, year). Below that grain, such as per day, a monthly target has no meaning: show it blank, or spread it by a rule you agree with the business.
Try it: split the target measure by 'Date'[Weekday]. What does it show, and why is that misleading? (Every weekday shows all the targets of the months visible, because a target can’t be split by weekday.)
4. Worked example
The store manager’s card row. Four cards: YTD 262k, Pace % +29 %, Target YTD 246k, vs Target % about +7 %. The YTD target is CALCULATE([Target], DATESYTD('Date'[Date])), the same pattern as YTD sales. Under them, a line chart of 3M Avg against Target by month. That’s the core of the capstone in Module 9.
5. Practice
- Write
Target YTD. - Write
Gap to Target= Target − Total Sales, blank when there is no target. - Why is a relationship between Targets[YearMonth] and Date[YearMonth] tricky?
Answers
CALCULATE([Target], DATESYTD('Date'[Date])).IF(ISBLANK([Target]), BLANK(), [Target] - [Total Sales]).- YearMonth is not unique in either table (many days per month in Date, many stores per month in Targets), so it would be many-to-many, which is slower and easy to get wrong.
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.