The request
From Laura Bennett, Finance Manager
“Our annual plan is in cumulative terms: $1 million by the end of June, $2.2 million by December. I need 2025 sales as a running total next to the monthly figure, so I can see the month we crossed each line.”
Your goal
The matrix has 'Date'[Year Month] on its rows and a 'Date'[Year] slicer on 2025. Create a measure called Sales YTD that returns Sales from January 1 up to the end of each row's month, with TOTALYTD and the 'Date'[Date] column.
- Fill
[Sales YTD]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], [Sales YTD]
- Slicers
- 'Date'[Year] = 2025
- Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), orders[status] = "completed")
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 | Sales YTD |
|---|---|---|
| 2025-01 | $150,571 | |
| 2025-02 | $99,903 | |
| 2025-03 | $166,787 | |
| 2025-04 | $189,734 | |
| 2025-05 | $206,469 | |
| 2025-06 | $213,062 | |
| 2025-07 | $191,967 | |
| 2025-08 | $182,987 | |
| 2025-09 | $143,379 | |
| 2025-10 | $174,599 | |
| 2025-11 | $292,364 | |
| 2025-12 | $288,346 | |
| 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
Year-to-date (YTD) is a running total that restarts every January 1. It is how plans, budgets and bonus targets are usually written, so it is one of the first measures a finance team asks for.
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 row, and checks every cell. In Power BI Desktop: Home → New measure, type
the formula, then put 'Date'[Year Month] on the matrix rows and a slicer with 'Date'[Year] on the
page.
TOTALYTD
Sales YTD = TOTALYTD([Sales], 'Date'[Date])- The first argument is the expression to accumulate, usually an existing measure.
- The second is the date column of the marked Date table.
- Optional third and fourth arguments: an extra filter, and a year-end date as text for fiscal
years, e.g.
TOTALYTD([Sales], 'Date'[Date], "6-30")for a year that ends on June 30.
What happens in one cell
Take the row 2025-04. The cell’s filter context holds the Year slicer (2025) and the row (Year Month = 2025-04), so the visible dates are April 1 to April 30.
- TOTALYTD finds the last visible date: April 30, 2025.
- It builds the dates from January 1, 2025 to April 30, 2025.
- It evaluates
[Sales]with that set of dates as the filter on'Date'[Date].
Step 3 is where the marked Date table matters. The row still says Year Month = 2025-04; if that
filter stayed, the January-to-April dates would be cut back to April. Because 'Date' is marked as
the date table, a filter on its date column replaces every other filter on the table, and the
running total sees all four months.
The Total row has the whole of 2025 visible, so its last date is December 31 and its YTD is the full-year figure, the same as Sales.
Running totals that restart
Put 'Date'[Year] on the rows above Year Month, remove the slicer, and the running total starts
again at each January: 2024-12 holds the 2024 total, 2025-01 only January 2025. That restart is
the difference between YTD and an all-time cumulative total, which needs a different pattern
(chapter 7).
Common mistakes
- The fact table’s date.
TOTALYTD([Sales], orders[order_date])uses a column with gaps and no marked table. Always pass'Date'[Date]. - Expecting YTD on a quarter or year row to be the sum of its months. It is the value at the last date of the period, not a sum of running totals.
- Future months. In a live report, months after today show the same YTD as the current month. Hide them with a filter on a past-dates flag on the calendar.
Lantern lit!
Practised: totalytdyear to daterunning totaldate tabletime intelligence
- Solved
- No hints
- First run