3.2Time intelligence: running totals (YTD) & previous-year comparison
The essentials in 30 seconds
- The No. 1 question of any management — "where do we stand vs last year?" — is answered with three functions: TOTALYTD (running total), SAMEPERIODLASTYEAR (same period last year) and DATEADD (generic offset).
- Absolute prerequisite: the marked date table — without it, these functions return wrong results, especially the running total and the comparison.
- The change is always expressed in value AND in %: the two measures go as a pair, and the whole forms the steering quartet.
Running mini-case — Zineb answers management at a glance
The management of Zineb's agri-food SME asks every Monday: "where do we stand compared with last year?". Rather than recalculating by hand, Zineb applies the section: she first checks that her date table is properly marked (absolute prerequisite), then builds the quartet — Sales YTD = TOTALYTD([Total Sales]; Dates[Date]) for the running total since January, Sales LY = CALCULATE([Total Sales]; SAMEPERIODLASTYEAR(Dates[Date])) for the same period a year earlier, then the change in value and in %. She places them in a Month × 4 measures matrix: the answer to management fits on a single visual, and the running total climbs before starting again in January.
Standalone case for this section: it returns in the course, the diagram, the script and the 48h challenge of section 3.2.
1. The three flagship functions
Time intelligence in DAX answers the No. 1 question of any management with three functions: TOTALYTD (the running total since the start of the year), SAMEPERIODLASTYEAR (the same period a year earlier) and DATEADD (the generic, more flexible offset). They never calculate "on their own": they move a period in time.
2. The non-negotiable prerequisite
Absolute prerequisite: the date table marked as a date table. Without it, these functions lie — an empty Sales LY or a wrong running total almost always comes from there. Zineb checks this setting before doubting her formula.
3. The steering quartet
We assemble the standard quartet of any management report: Sales YTD, Sales LY, change in value and change in %. These measures are built once, properly, and copied from model to model. The change is always expressed in value AND in %: the two go as a pair.
🔎 Concrete example — 🔎 Concrete example — The time measures
The standard quartet, reusable on all of Zineb's models:
Revenue YTD = TOTALYTD([Total Revenue], Dates[Date])
Revenue PY = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Dates[Date]))
Growth = [Total Revenue] - [Revenue PY]
Growth % = DIVIDE([Total Revenue] - [Revenue PY], [Revenue PY]) // % formatReading: TOTALYTD cumulates since January and starts again at zero at the beginning of the year; SAMEPERIODLASTYEAR shifts the calculation one year back.
Lesson: DIVIDE([Total Sales] - [Sales LY]; [Sales LY]) gives the relative gap — the change goes as a pair, value + %.
Section vocabulary
- Time intelligence
- The family of DAX functions that manipulate periods (running totals, offsets, comparisons), all based on the date table.
Simple example: Zineb uses it for the SME's annual running total and previous-year comparison.
What it is not: a simple date filter placed by hand on a visual.
Reflex: compare periods → time intelligence.
- TOTALYTD
- The annual running total since the start of the year: TOTALYTD([Total Sales]; Dates[Date]).
Simple example: Zineb tracks the cumulative sales since January for management.
What it is not: the month's sales alone, with no running total.
Reflex: annual running total → TOTALYTD.
- SAMEPERIODLASTYEAR
- The same period a year earlier: CALCULATE([Total Sales]; SAMEPERIODLASTYEAR(Dates[Date])).
Simple example: Zineb compares the current month with the same month last year.
What it is not: any period with no date reference.
Reflex: previous-year comparison → SAMEPERIODLASTYEAR.
- DATEADD
- The generic offset: DATEADD(Dates[Date]; -1; MONTH) for the previous month — more flexible than the dedicated functions.
Simple example: Zineb uses it for a tailor-made offset.
What it is not: a function frozen on a single type of period.
Reflex: tailor-made offset → DATEADD.
- Previous-year change
- The final duo: Change = [Sales] - [Sales LY] and Change % = DIVIDE([Sales] - [Sales LY]; [Sales LY]).
Simple example: Zineb displays the gap in value and in % side by side.
What it is not: a change in value alone, with no relative magnitude.
Reflex: always value AND percentage.
In practice — In practice — The steering quartet
- Create Sales YTD = TOTALYTD([Total Sales]; Dates[Date]) and check in a Month × measures matrix: the running total climbs then starts again in January, as with Zineb.
- Create Sales LY = CALCULATE([Total Sales]; SAMEPERIODLASTYEAR(Dates[Date])) (requires history over two years — duplicate the sales shifted by one year in the CSV if needed).
- Complete Change % = DIVIDE([Total Sales] - [Sales LY]; [Sales LY]) in % format.
- Assemble the steering matrix: Month in rows, the 4 measures as values — the answer to "where do we stand?" fits on one visual.
Step-by-step script — Zineb's quartet (10 min)
1) Check that your date table is marked as a date table (Table tools). 2) Create Sales YTD = TOTALYTD([Total Sales], Dates[Date]). 3) Create Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date])). 4) Add Change = [Total Sales] - [Sales LY] and Change % = DIVIDE([Total Sales] - [Sales LY], [Sales LY]) in %. 5) Place Month in rows and the 4 measures as values: read the answer to management at a single glance.
An empty Sales LY? Check in order: date table marked, active relationship on the right column, history covering the previous year.
Copy-paste template — the time quartet
Revenue YTD = TOTALYTD([Total Revenue], Dates[Date])
Revenue PY = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Dates[Date]))
Growth = [Total Revenue] - [Revenue PY]
Growth % = DIVIDE([Total Revenue] - [Revenue PY], [Revenue PY]) // %
Prerequisite: date table MARKED. Reflex: growth = value + %.Annual running total
Zineb tracks the cumulative sales since January (TOTALYTD): the curve climbs then starts again each new year.
Same month last year
Zineb compares the current month with the same month a year earlier (SAMEPERIODLASTYEAR) to smooth seasonality.
Previous month
Zineb shifts by one month with DATEADD to follow the short-term trend.
Shifted fiscal year
Closing in June: Zineb passes the fiscal year-end date to TOTALYTD to respect the SME's fiscal year.
✓ Best practices
- Rely on the marked date table: it is the non-negotiable prerequisite.
- Build these measures once, properly: they are copied from model to model.
- Express the change in value AND in % (the two go as a pair).
✗ Common pitfalls
- Forgetting to mark the date table (the functions lie).
- Routing the date relationship through the wrong column.
- Expecting a Sales LY with no history covering the previous year.
Key takeaways
- All time intelligence rests on the MARKED date table: the prerequisite was not optional.
- TOTALYTD, SAMEPERIODLASTYEAR, DATEADD: three functions cover the usual requests.
- The change is expressed in value AND in %: the two measures go as a pair.
- Build these measures once, properly: they are copied from model to model throughout your career.
48h challenge — apply it tomorrow
Build Zineb's steering quartet on your data: Sales YTD, Sales LY, change in value and in %. Place it in a Month × measures matrix, then verify that the running total does start again in January and that your date table is marked.
- I can write an annual running total with TOTALYTD.
- I can compare a period with the previous year using SAMEPERIODLASTYEAR.
- I know the prerequisite: the marked date table.
Frequently asked questions
My Sales LY shows empty or wrong: why?
Three suspects, to check in this order (one of them is the cause 95% of the time): the date table is not marked as a date table; the relationship goes through the wrong date column (or is inactive); the history does not cover the previous year (January's LY is then empty, which is normal). Always start with the marked date table.
How do I handle a shifted fiscal year (closing in June)?
TOTALYTD accepts a third argument, the fiscal year-end date: TOTALYTD([Sales]; Dates[Date]; "30/06") cumulates since 1 July. For more complex needs, we enrich the date table with fiscal columns (FiscalYear, FiscalMonth) — the beauty of having your own controlled calendar table.
What is the absolute prerequisite for time intelligence to work?