The request
From Chris Morgan, Promotions Lead
“November is our Black Friday month. I want a day-by-day view of November 2025: each day's sales and the month's running total so far, so I can compare the pace with last November's $319,000.”
Your goal
The matrix has 'Date'[Date] on its rows and a 'Date'[Year Month] slicer on 2025-11. Create a measure called Sales MTD: Sales from the first of the month up to each row's day. Write it with CALCULATE and DATESMTD('Date'[Date]).
- Fill
[Sales MTD]on every row of the visualnot done yet - The Total row right toonot done yet
Your measure
- Visual
- A matrix: rows = 'Date'[Date]; values = [Sales], [Sales MTD]
- Slicers
- 'Date'[Year Month] = 2025-11
- 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
| Date | Sales | Sales MTD |
|---|---|---|
| 2025-11-01 | $13,803 | |
| 2025-11-02 | $9,265 | |
| 2025-11-03 | $8,846 | |
| 2025-11-04 | $4,958 | |
| 2025-11-05 | $6,880 | |
| 2025-11-06 | $6,287 | |
| 2025-11-07 | $6,646 | |
| 2025-11-08 | $5,912 | |
| 2025-11-09 | $7,314 | |
| 2025-11-10 | $13,755 | |
| 2025-11-11 | $6,441 | |
| 2025-11-12 | $10,065 | |
| 2025-11-13 | $5,885 | |
| 2025-11-14 | $5,975 | |
| 2025-11-15 | $16,267 | |
| 2025-11-16 | $8,817 | |
| 2025-11-17 | $10,937 | |
| 2025-11-18 | $15,139 | |
| 2025-11-19 | $6,540 | |
| 2025-11-20 | $12,074 | |
| 2025-11-21 | $10,664 | |
| 2025-11-22 | $6,574 | |
| 2025-11-23 | $14,407 | |
| 2025-11-24 | $5,660 | |
| 2025-11-25 | $4,173 | |
| 2025-11-26 | $4,801 | |
| 2025-11-27 | $11,165 | |
| 2025-11-28 | $15,976 | |
| 2025-11-29 | $19,382 | |
| 2025-11-30 | $17,756 | |
| Total | $292,364 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Chris gets, and why
The lesson
The TOTAL…TD functions are shortcuts. Underneath each one is CALCULATE with a table of dates, and
knowing that form lets you combine dates with any other filter.
What you are working in
This page runs a practice DAX engine built for this site. It evaluates your measure for each day of
November 2025 and the Total row, and checks every cell. In Power BI Desktop: Home → New measure,
type the formula, put 'Date'[Date] on the rows (switch it from the date hierarchy to the plain
column in the field well’s drop-down), and add a slicer on 'Date'[Year Month].
DATESMTD returns dates
Sales MTD = CALCULATE([Sales], DATESMTD('Date'[Date]))DATESMTD('Date'[Date]) is a table function: it looks at the last date visible in the current
filter context and returns every date from the first of that month up to it. In the row for
November 12, 2025 it returns twelve dates, November 1 to 12. CALCULATE then uses that table as a
filter on 'Date'[Date], and [Sales] adds up those twelve days.
The family has three members, one per period:
| Dates function | Shortcut | Starts at |
|---|---|---|
DATESMTD('Date'[Date]) |
TOTALMTD([Sales], 'Date'[Date]) |
the 1st of the month |
DATESQTD('Date'[Date]) |
TOTALQTD([Sales], 'Date'[Date]) |
the 1st of the quarter |
DATESYTD('Date'[Date]) |
TOTALYTD([Sales], 'Date'[Date]) |
January 1 |
TOTALMTD([Sales], 'Date'[Date]) is exactly CALCULATE([Sales], DATESMTD('Date'[Date])). Use
whichever reads better; many teams standardise on the CALCULATE form because every time measure
then looks the same.
Why the long form is worth knowing
CALCULATE takes any number of filters, so dates combine with everything else you learned in the previous chapter:
App Sales MTD = CALCULATE([Sales], DATESMTD('Date'[Date]), orders[channel] = "app")And the dates function can be replaced by another one without touching the rest: swap DATESMTD for DATESQTD and the same measure becomes quarter-to-date.
Reading the result
The running total passes $100,000 on November 12 and $200,000 on the 23rd, and the last three days, Black Friday weekend, add about $53,000 more. The Total row has all thirty days visible, so its month-to-date is the full November figure, about $292,000: short of last November’s $319,000. The next level puts last year’s figure in a measure of its own.
Common mistakes
- The wrong period.
DATESYTDinstead ofDATESMTDstarts from January. - A date hierarchy on the rows. Power BI’s automatic Year/Quarter/Month/Day hierarchy is built on hidden tables; for time intelligence, use your own Date table’s columns.
- DATESMTD on its own. It returns a table, not a number. It only makes sense inside CALCULATE (or a function that takes a table).
Lantern lit!
Practised: datesmtdtotalmtdmonth to datecalculatetime intelligence
- Solved
- No hints
- First run