Time intelligence functions, TOTALYTD, SAMEPERIODLASTYEAR, DATEADD and their relatives, are DAX’s shortcuts for year-to-date, quarter-to-date and year-over-year style calculations. Every result below is run for real against the Excel Data Model, the same VertiPaq engine Power BI uses, over one small Sales table spanning two order years, 2023 and 2024.
In this guide
- TOTALYTD, TOTALQTD and TOTALMTD
- DATESYTD, and what happens with no other filter
- Year-over-year growth, and the BLANK that DIVIDE catches
- SAMEPERIODLASTYEAR versus DATEADD
- Why these functions need a real date table
- Fiscal years, and measure versus calculated column
- Common mistakes
- Cheat sheet
- Try it yourself
- FAQ
TOTALYTD, TOTALQTD and TOTALMTD
Each of these resets at the start of a different period and accumulates up to the given date. Measured as of March 18, 2024:
| Measure | Resets at | Result as of March 18, 2024 |
|---|---|---|
| TOTALYTD(SUM(Sales[Amount]), Dates[Date]) | January 1 | 840 – every 2024 order so far: 100, 250, 40, 150, 300 |
| TOTALQTD(SUM(Sales[Amount]), Dates[Date]) | Start of the current quarter | 840 – March 18 is still inside Q1, so QTD equals YTD at this date |
| TOTALMTD(SUM(Sales[Amount]), Dates[Date]) | Start of the current month | 450 – only the two March orders, 150 and 300 |
Moving the same YTDAmt measure to earlier dates shows the reset in action: as of January 20, 2024 it returns 140 (only the two January orders, 100 and 40, had happened yet), and as of December 28, 2023, the last day of the prior order year, it returns 250 (120 + 80 + 50, the three 2023 orders), since TOTALYTD restarts every January 1 rather than accumulating across years. The equivalent MTDAmt measure as of February 10, 2024 returns 250, since that single order is the only one that month so far.
DATESYTD, and what happens with no other filter
DATESYTD(Dates[Date]) returns a table of dates rather than a number, meant to be used as a CALCULATE filter argument: CALCULATE(COUNTROWS(Sales), DATESYTD(Dates[Date]), Dates[Date] <= DATE(2024,3,18)) returns 5, the count of 2024 orders on or before that date.
Used with no other date filter active at all, CALCULATE(SUM(Sales[Amount]), DATESYTD(Dates[Date])) does not error or return BLANK: it resolves year-to-date relative to the latest date the Dates table currently allows, which with nothing else narrowing the context is the table’s own final day. DATESYTD always needs an end point to work from, even when nothing else is filtering it.
Year-over-year growth, and the BLANK that DIVIDE catches
A growth-percentage pattern, naming each period’s total with VAR and comparing them, is one of the most common real uses of time intelligence:
VAR cur = <this period's total>
VAR prev = <the same period, one year earlier>
RETURN DIVIDE(cur - prev, prev)
| Comparison | cur | prev | DIVIDE(cur – prev, prev) |
|---|---|---|---|
| March 2024 vs March 2023 | 450 | 80 | 4.625 – March grew by about 462 percent |
| January 2024 vs January 2023 | 140 | BLANK (no 2023 January orders) | BLANK – DIVIDE's safe zero-denominator behaviour |
The isolated March-2023 total itself, 80, comes from shifting the current date range back exactly 12 months with EDATE(DATE(2024,3,1), -12) and EDATE(DATE(2024,3,31), -12), which is the manual version of what SAMEPERIODLASTYEAR does automatically. The second row matters just as much as the first: January 2023 had zero orders, so prev is BLANK, and DIVIDE, not the plain / operator, is exactly what keeps that case from erroring out the whole visual, returning a clean BLANK instead.
SAMEPERIODLASTYEAR versus DATEADD
| Function | What it does |
|---|---|
| SAMEPERIODLASTYEAR(Dates[Date]) | Shifts whatever date range is currently in context back by exactly one year, keeping the same span of days |
| DATEADD(Dates[Date], -1, YEAR) | Produces the same one-year-back shift here, but is a general-purpose function that takes a unit argument |
| DATEADD(Dates[Date], -1, MONTH) | Shifts back one month instead, turning a year-over-year comparison into a month-over-month one |
SAMEPERIODLASTYEAR and DATEADD(..., -1, YEAR) produce the same shifted range in this case, but DATEADD is the more general tool: swapping its unit argument from YEAR to MONTH, QUARTER or DAY covers comparisons SAMEPERIODLASTYEAR cannot express at all, since it is fixed to exactly one year back.
Why these functions need a real date table
Every function on this page reasons about calendar periods, not just the dates that happen to exist in the fact table. A date column built only from the days an order actually happened has gaps, which breaks the year-to-date or same-period logic these functions depend on to walk a continuous calendar, since they cannot correctly find a whole year, quarter or shifted period across missing days. This is exactly why a dedicated date table, one row per calendar day with no gaps, is a prerequisite, not an optional nicety.
A properly built date table is also usually spanned across full calendar years, including years with no transactions yet, rather than stopping at the latest order date. If the table stopped exactly at the last order, a function trying to shift a year forward, or compare against a period just past the data’s edge, would have no dates left to work with.
Fiscal years, and measure versus calculated column
TOTALYTD assumes a calendar year starting January 1 by default. A business whose fiscal year starts elsewhere, such as April, is not locked out: TOTALYTD accepts an optional year-end-date argument specifically so a different fiscal year start still produces a correct year-to-date figure.
Why this belongs in a measure, not a calculated column
A calculated column is fixed at refresh time for its own row, so a TOTALYTD-style calculated column could not react to a date range a viewer later selects by clicking through months in the report. A measure recalculates live against whatever filter context a viewer creates, which is exactly the behaviour a year-to-date figure needs.
Common mistakes
- Using a bare fact-table date column instead of a real date table. Gaps in that column silently produce wrong or partial year-to-date and same-period results.
- Dividing growth with the plain / operator. A period with zero prior-year activity gives a zero denominator; DIVIDE returns BLANK safely instead of erroring.
- Reaching for SAMEPERIODLASTYEAR for a month-over-month comparison. It only shifts by one year; DATEADD with MONTH as the unit is the right tool.
- Building a date table that stops at the last order date. A shift that needs to look past the data’s edge then has nothing to work with.
- Putting a YTD-style calculation in a calculated column. It freezes at refresh time and cannot react to a viewer’s date selection the way a measure does.
Cheat sheet
| Question | Answer |
|---|---|
| What resets every January 1? | TOTALYTD |
| What resets at the start of the current quarter? | TOTALQTD |
| What resets at the start of the current month? | TOTALMTD |
| What shifts a date range back exactly one year? | SAMEPERIODLASTYEAR, or DATEADD(…, -1, YEAR) |
| What function takes a unit argument (YEAR, MONTH, QUARTER, DAY)? | DATEADD |
| What keeps a zero prior-period growth calculation from erroring? | DIVIDE |
| What do these functions need to avoid gaps? | A dedicated, contiguous date table |
| What argument handles a non-January fiscal year start? | TOTALYTD's optional year-end-date argument |
| Where should a YTD-style calculation live? | A measure, not a calculated column |
Try it yourself
Quick check
Using the same Sales table, what would TOTALQTD(SUM(Sales[Amount]), Dates[Date]) return as of February 10, 2024, and why is it the same number as TOTALYTD on that date?
Show answer
140, the same as TOTALYTD: 100 (January 15) plus 40 (January 20), since the February 10 order (250) has not happened yet in this comparison and February 10 is still inside the first quarter of 2024. TOTALQTD and TOTALYTD only diverge once a date falls later in the quarter than it does in the year, which does not happen in Q1, since the quarter and the year-to-date both start on January 1.
Frequently asked questions
What is time intelligence in DAX?
Time intelligence is a family of DAX functions, TOTALYTD, TOTALQTD, TOTALMTD, SAMEPERIODLASTYEAR, DATEADD and related functions, that compute period-to-date totals and shifted comparison periods, such as the same month a year earlier.
What is the difference between TOTALYTD and TOTALQTD?
TOTALYTD accumulates from the start of the current calendar year, while TOTALQTD accumulates only from the start of the current quarter, so a date late in a quarter but early in the year will show very different totals from each.
What is the difference between SAMEPERIODLASTYEAR and DATEADD?
SAMEPERIODLASTYEAR is a shortcut that always shifts the current date range back exactly one year. DATEADD is more general, taking a unit argument (YEAR, QUARTER, MONTH or DAY), so it can also produce a month-over-month or quarter-over-quarter shift that SAMEPERIODLASTYEAR cannot.
Why do DAX time intelligence functions need a dedicated date table?
They reason about continuous calendar periods, not just the dates present in the fact table. A date column with gaps, such as only the days an order happened, can make a year-to-date or same-period calculation silently wrong or incomplete.
How does TOTALYTD handle a fiscal year that does not start in January?
TOTALYTD accepts an optional year-end-date argument, letting a business with a different fiscal year start, such as April, still get a correct year-to-date figure instead of one assuming a January start.
Why use DIVIDE instead of / when calculating year-over-year growth in DAX?
A period with zero activity in the prior year produces a zero denominator, which the plain / operator would error on. DIVIDE returns BLANK safely in that case instead of breaking the calculation.
Test yourself
Timed questions on DAX Time Intelligence, with an explanation for every answer.