The request
From Laura Bennett, Finance Manager
“The YTD column is in the board pack now, thanks. The next question was obvious: are we ahead of last year at the same point? For each month of 2025 I need last year's year-to-date up to the same month, next to ours.”
Your goal
The matrix has 'Date'[Year Month] on its rows, a 'Date'[Year] slicer on 2025, and the given measure Sales YTD. Create a measure called Sales YTD LY: last year's year-to-date Sales up to the same point, with CALCULATE, DATESYTD and SAMEPERIODLASTYEAR.
- Fill
[Sales YTD LY]on every row of the visualnot done yet - The Total row right toonot done yet
Your measure
- Visual
- A matrix: rows = 'Date'[Year Month]; values = [Sales YTD], [Sales YTD LY]
- Slicers
- 'Date'[Year] = 2025
- Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), orders[status] = "completed")Sales YTD = TOTALYTD([Sales], 'Date'[Date])
Data pane: tables and columns
- order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
- orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
- products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
- customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
- returns return_id, order_item_id, returned_on, reason, refund_amount
- Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday
Loading the editor…
Reference solution
| Year Month | Sales YTD | Sales YTD LY |
|---|---|---|
| 2025-01 | $150,571 | |
| 2025-02 | $250,474 | |
| 2025-03 | $417,261 | |
| 2025-04 | $606,996 | |
| 2025-05 | $813,465 | |
| 2025-06 | $1,026,527 | |
| 2025-07 | $1,218,494 | |
| 2025-08 | $1,401,481 | |
| 2025-09 | $1,544,860 | |
| 2025-10 | $1,719,459 | |
| 2025-11 | $2,011,823 | |
| 2025-12 | $2,300,170 | |
| Total | $2,300,170 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Laura gets, and why
The lesson
A running total on its own says how far you’ve come. The board wants to know whether that is more or less than last year at the same point. That needs two time functions working together.
What you are working in
This page runs a practice DAX engine built for this site. It evaluates your measure for each month of 2025 and the Total, and checks every cell. In Power BI Desktop: Home → New measure, type the formula, and place it next to Sales YTD in the matrix.
Nest the functions: shift, then extend
Sales YTD LY = CALCULATE([Sales], DATESYTD(SAMEPERIODLASTYEAR('Date'[Date])))Read it from the inside out, for the 2025-04 row:
- The visible dates are April 1–30, 2025.
SAMEPERIODLASTYEAR('Date'[Date])returns April 1–30, 2024.DATESYTD(…)takes that table as its input: the last date is April 30, 2024, so it returns January 1 – April 30, 2024.- CALCULATE evaluates
[Sales]on those dates.
Time-intelligence functions accept a table of dates wherever they accept a date column, which is what makes nesting possible.
Or shift a measure you already have
Sales YTD LY = CALCULATE([Sales YTD], SAMEPERIODLASTYEAR('Date'[Date]))CALCULATE sets the context to April 2024, then the Sales YTD measure runs inside that context and builds January–April 2024 itself. Same numbers, and if someone later changes Sales YTD (a fiscal year-end, say), the LY version follows automatically. Many teams prefer this form for that reason.
The trap: two filter arguments
This version looks reasonable and is common in course notes and forum answers:
CALCULATE([Sales], DATESYTD('Date'[Date]), SAMEPERIODLASTYEAR('Date'[Date]))Each filter argument is evaluated separately, in the original context, and CALCULATE then applies
all of them together. DATESYTD gives January–April 2025, SAMEPERIODLASTYEAR gives April 2024.
Both filter 'Date'[Date], so the result is their intersection: no dates at all, and Power BI shows
a blank. Filters in separate arguments combine with AND; to chain two transformations, nest them.
Reading the result
| Year Month | Sales YTD | Sales YTD LY |
|---|---|---|
| 2025-03 | $417,261 | $348,330 |
| 2025-06 | $1,026,527 | $789,588 |
| 2025-09 | $1,544,860 | $1,234,547 |
| 2025-12 | $2,300,170 | $1,993,604 |
The 2025 running total stays above last year’s at every month-end. The lead is largest in relative terms in early summer (30% ahead in June) and narrows to 15.4% by December after a soft September and November, a nuance a single full-year number wouldn’t show.
Common mistakes
- Separate arguments instead of nesting. See above: a blank.
- Shifting the wrong way.
DATEADD('Date'[Date], 1, YEAR)looks forward; last year is −1. - Partial current month. In a live report the current month is incomplete. Compare up to the same day, not the same month, if the business needs that precision.
Lantern lit!
Practised: datesytdsameperiodlastyearytd last yearcalculatetime intelligence
- Solved
- No hints
- First run