Time intelligence is the point where a lot of people decide they hate DAX. They can write a SUM, they can make a measure, they're feeling capable — and then someone asks for year-to-date sales compared to the same period last year, they reach for the time-intelligence functions, and everything breaks in ways that make no sense. The numbers come out blank, or wrong, or right in one visual and wrong in another. The functions have names that promise exactly what you want — TOTALYTD, SAMEPERIODLASTYEAR, DATEADD — and yet they refuse to behave. Almost every time, the cause is the same single thing nobody warned them about, and once you understand it, time intelligence stops being a source of tears and becomes almost boringly reliable. Let me save you the crying.

The one thing that fixes almost everything: a proper date table

Here it is, the secret that isn't really a secret but somehow never gets said early enough: DAX time intelligence needs a dedicated, continuous, marked date table, and it will not work properly without one. Not a date column on your sales table. A separate table, with one row for every single day across the full range of your data — no gaps — related to your fact table, and explicitly told "you are the date table." Nearly every mysterious time-intelligence failure I've ever debugged came down to this being missing, incomplete, or not marked. Get this right and the functions come to life. Get it wrong and they will fight you forever.

Why does it matter so much? Because the time-intelligence functions work by manipulating a complete set of dates — they take the dates currently in context, shift or expand them, and recompute. If your date table has gaps (only dates where a sale happened, say), the functions have holes to fall into: "same period last year" can't find last year's dates if last year's quiet days don't exist in the table. A continuous date table gives the functions solid ground to stand on. The three rules:

  • It must be continuous — every day in the range, including weekends, holidays, and days with no activity.
  • It must cover full years — from 1 January of your earliest year to 31 December of your latest, or the year-based functions misbehave at the edges.
  • It must be marked as a date table — right-click, "Mark as date table," and point it at your date column. This is the step everyone skips and then wonders why nothing works.

Then the functions just work

With a real date table related to your facts, the rest falls into place. Year-to-date is a one-liner:

YTD Sales = TOTALYTD([Total Sales], 'Date'[Date])

Same period last year, for comparisons:

Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

And a year-over-year change built from those two:

Sales YoY % =
DIVIDE([Total Sales] - [Sales PY], [Sales PY])

A rolling twelve months, the one people find fiddliest, is just DATESINPERIOD doing the shifting:

Sales R12M =
CALCULATE(
    [Total Sales],
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
)

None of these are doing anything clever. They lean entirely on the date table being correct. That's the whole point I'm making: the functions were never the hard part. The foundation under them was.

Building the date table itself

If you don't already have a date table, DAX will make you one in a single line. CALENDARAUTO() scans your model and generates a continuous range covering all the dates it finds; CALENDAR(start, end) lets you set the bounds explicitly when you want control. Wrap either in ADDCOLUMNS to bolt on the year, month, quarter, and month-name columns your visuals will want to slice and sort by:

Date =
ADDCOLUMNS(
    CALENDAR(DATE(2018,1,1), DATE(2025,12,31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "MonthNo", MONTH([Date]),
    "Quarter", "Q" & FORMAT([Date], "Q")
)

Then relate it to your fact table's date, mark it as the date table, and you're on solid ground. One thing to watch: sort your month-name column by its month number, or the report will cheerfully order your months alphabetically — April, August, December — which is its own small source of tears.

The gotchas that remain

Even with a good date table, a couple of things still catch people, so know them in advance:

  • Build measures on measures. Write [Total Sales] once and reference it inside your time-intelligence measures rather than repeating the underlying logic. When the base calculation changes, everything built on it updates, and you're not maintaining the same logic in ten places.
  • Watch the relationship. The functions act on the date table, and the filter has to reach your facts through an active relationship. If the relationship is missing or inactive, the numbers won't propagate and you'll be baffled.
  • Incomplete current periods. Year-to-date at any point mid-year is correct but partial — make sure your comparison is like-for-like (this year-to-date versus the same span last year), or you'll compare a half-year against a full one and panic.
  • Mind the fiscal year. If your organisation's year doesn't start in January, the plain functions assume a calendar year. There are fiscal-aware variants and a year-end date argument — reach for those rather than fighting the defaults.

The lesson under the lesson

If there's a moral here beyond "make a date table," it's the one that keeps recurring in this work: the clever, headline feature almost always depends on an unglamorous foundation that nobody demos. Time intelligence looks like it's about knowing exotic functions, and it turns out to be about doing one boring piece of modelling correctly. Build the date table properly — continuous, complete, marked — and the tears stop, because the functions were only ever as reliable as the ground you gave them to stand on. Give them good ground, and they'll do exactly what their names promise.