Module 6: Time intelligence · Lesson 6.1 · about 15 min
Year, quarter and month to date
Running totals that reset each year, quarter or month. Use TOTALYTD and DATESYTD, see which dates they keep for each cell, and handle fiscal years.
Module 6 · about 15 minutes · read first: 5.2 and 3.3. You’ll be able to write year-, quarter- and month-to-date measures and explain how they choose dates.
1. Why this matters
“Year to date” is on almost every sales dashboard and in nearly every BI job interview. It’s also the gentlest introduction to time intelligence: CALCULATE with a table of dates as the filter.
2. See it climb
Write Sales YTD with TOTALYTD([Total Sales], 'Date'[Date]). It is split by YearMonth: watch it climb through each year and start again in January.
Press Run to see the result.
TOTALYTD(measure, date column): TOTALYTD([Total Sales], 'Date'[Date]).
Total Sales = SUM(Sales[Amount])
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date]) Trace March 2025: the visual’s filter is the 31 days of March; CALCULATE replaces it with the dates from 1 January to 31 March, and the sum runs over those.
3. The functions
| Measure | Code |
|---|---|
| Year to date | TOTALYTD([Total Sales], 'Date'[Date]) or CALCULATE([Total Sales], DATESYTD('Date'[Date])) |
| Quarter to date | TOTALQTD(…) or CALCULATE([Total Sales], DATESQTD('Date'[Date])) |
| Month to date | TOTALMTD(…) or CALCULATE([Total Sales], DATESMTD('Date'[Date])) |
| Fiscal year to date (year ends 31 March) | TOTALYTD([Total Sales], 'Date'[Date], "3-31") |
Write Sales QTD using CALCULATE with DATESQTD('Date'[Date]). Trace a month to see which dates it keeps.
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
Sales QTD = CALCULATE([Total Sales], DATESQTD('Date'[Date])) Prefer the CALCULATE form in real models: you can add filters (CALCULATE([Total Sales], DATESYTD('Date'[Date]), Store[Region] = "West")) and it reads the same as every other measure.
4. Two things that trip people up
- The total row. With no date filter, the last visible date is 31 December 2025, so YTD at the total shows 2025’s full year. That’s correct but surprising: consider hiding it or labelling it.
- Future dates. A date table that runs to 31 December shows YTD rising flat through months that haven’t happened yet. Limit it with
IF(MAX('Date'[Date]) <= TODAY(), …)or a column marking dates with data.
Principle
Principle: time intelligence functions are table functions that return a set of dates, computed from the dates visible in the current filter context. CALCULATE then uses that set as the filter on the date column (replacing every other Date filter, because the table is marked as a date table).
where is the last visible date.
5. Worked example
Northbay, end of March 2025. Sales: January 18,238, February 13,369, March 21,792. YTD on the March row: 18,238 + 13,369 + 21,792 = 53,400 (rounded), which matches the editor. On the April row the dates run to 30 April and the YTD grows by April’s sales.
6. Practice
- Write
Sales MTD. - Write
West YTD: year-to-date sales for the West region only. - Split by Year (not month). What does YTD show for 2024, and why?
Answers
CALCULATE([Total Sales], DATESMTD('Date'[Date])).CALCULATE([Total Sales], DATESYTD('Date'[Date]), Store[Region] = "West").- 2024’s full-year sales: the last visible date of 2024 is 31 December, so YTD covers the whole 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.