DAX Context Demystified, Part 3: Time Intelligence and Why Your YTD Is Probably Wrong

What you carried over from Parts 1 and 2

Two ideas need to be active in your head before any of this makes sense.

From Part 1: filter context is the set of constraints active across the model at evaluation time. Every visual, slicer, and report filter contributes to it.

From Part 2: CALCULATE modifies that filter context, and most of what looks like time intelligence behaviour is really filter context being rewritten. Filters on the same column replace, filters on new columns add, and the whole thing is governed by execution order: filter arguments first, expression second.

Time intelligence functions are CALCULATE wrappers. That is the entire trick. Once you read them as syntactic sugar over CALCULATE plus a date filter, the failure modes become obvious because they're the same failure modes you already know.

Time intelligence is just CALCULATE in disguise

Take TOTALYTD. The intuitive read is "give me the year-to-date sum." The actual definition is closer to:

TOTALYTD([Total Sales], 'Date'[Date]) = CALCULATE( [Total Sales], DATESYTD('Date'[Date]) )

DATESYTD returns a single-column table of every date from the start of the current year up to the latest date in the current filter context. CALCULATE then takes that table as a filter argument and evaluates [Total Sales] against it.

SAMEPERIODLASTYEAR is identical in shape:

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

SAMEPERIODLASTYEAR takes the dates currently in the filter context and shifts them back by one year, returning that shifted set of dates. CALCULATE then applies it as a filter.

This is the mental model worth carrying: every time intelligence function is a table-valued function that returns a set of dates, plus a CALCULATE that applies those dates as a filter. Nothing more. The "intelligence" is the date arithmetic. The filter mechanics are vanilla CALCULATE.

Which means every CALCULATE rule from Part 2 still applies. New filter on 'Date'[Date] replaces the old filter on 'Date'[Date]. New filter on 'Date'[Date] does not automatically replace filters on 'Date'[Year] or 'Date'[Month]. That asymmetry is where most of the mess comes from.

Why TOTALYTD goes wrong: the future dates problem

The most common time intelligence bug isn't a syntax issue, it's a data shape issue. Your YTD figure is too high, and the cause is almost always your date table.

The behaviour: it's April. Your YTD measure shows the same number as your full-year sales. The April column shows the December value.

The cause: DATESYTD returns dates from the start of the year to the latest date in the current filter context. If your filter context spans the full year (because nothing has narrowed it), DATESYTD happily returns every date from 1 January to 31 December. CALCULATE then sums sales over that entire range. There is no implicit "up to today" anywhere in the function.

If your date table extends into the future and the filter context is unconstrained, you'll get the full year back. Every time. The function is doing exactly what it was told.

The fix is at the date table, not the measure:

-- In your Date table, exclude future dates from anything that should -- respect "up to today." The cleanest approach is a flag column: 'Date'[IsPastOrToday] = 'Date'[Date] <= TODAY()

Then either filter visuals on IsPastOrToday = TRUE, or wrap the measure:

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

The flag column approach is preferable. It keeps the logic in the model and stops every measure from having to remember the filter.

What "Mark as Date Table" actually does

Marking a table as a date table isn't cosmetic. It changes how the engine handles time intelligence calls.

When you mark a table as a date table on column X, the engine does two things:

1. It validates that X contains contiguous, unique dates with no gaps.
2. It treats time intelligence functions called against X as if they were also calling ALL('Date') first.

That second behaviour is the one that matters. Without "Mark as Date Table," a function like DATESYTD doesn't strip filters on other columns of the date table. With it marked, DATESYTD effectively starts by clearing all filters on the date table, then applying the YTD date set as the only constraint.

This is why time intelligence measures sometimes "work fine" until someone adds a slicer for 'Date'[Year] and suddenly they break. If the table isn't marked as a date table, that Year slicer survives the time intelligence call and constrains the result alongside the date set. The two filters compound, and you get an empty or near-empty result.

The diagnostic: if removing or changing a filter on the date table changes whether your time intelligence works, the table almost certainly isn't marked. Marking it fixes the surprise.

Why filtering on the fact table date column breaks SAMEPERIODLASTYEAR

This one is a quiet killer. The measure looks correct. The result is wrong, but only sometimes.

The setup: your fact table has an OrderDate column. There's a relationship from Sales[OrderDate] to 'Date'[Date]. Someone, somewhere, drops Sales[OrderDate] onto a slicer or a visual axis instead of 'Date'[Date].

The measure:

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

What happens: SAMEPERIODLASTYEAR shifts the dates currently in the filter context, but it only shifts dates on 'Date'[Date]. The filter on Sales[OrderDate] is still in place, untouched. CALCULATE applies the shifted 'Date'[Date] filter, the relationship propagates that to the fact table, and then runs into the existing Sales[OrderDate] filter, which still points at the current period.

The intersection of "current period via the fact column" and "previous year via the date dimension" is empty. Your LY measure returns blank. Sometimes it returns a partial number, depending on overlap. It looks like a relationship issue. It isn't.

The rule: time intelligence is built to operate on date dimension columns. Filter, slice, and visualise off the date dimension. The fact table date column exists for the relationship and nothing else.

The fiscal year you forgot to set

TOTALYTD, DATESYTD, STARTOFYEAR, and friends all default to a calendar year ending 31 December. Most businesses don't.

The optional <year_end_date> argument exists for exactly this. It's a string in "MM-DD" format:

Sales FYTD = TOTALYTD( [Total Sales], 'Date'[Date], "06-30" -- Australian financial year ends 30 June )

The argument is easy to miss because it's optional and most documentation examples skip it. If your fiscal year doesn't end on 31 December and your YTD numbers reset on the wrong date, this is the cause.

A note on consistency: setting it on every measure is fragile. The more disciplined pattern is a fiscal-aware date table with a FiscalYear, FiscalMonth, and IsFiscalYTD column, then writing measures against those. Time intelligence functions stop being needed at all because the columns already encode the periods you care about. That's the path most production models end up on.

Inactive relationships and the silent failure mode

When you have multiple date columns on a fact table - typically OrderDate and ShipDate - only one relationship to 'Date' can be active at a time. The other sits inactive until activated by USERELATIONSHIP.

Time intelligence functions don't honour the inactive relationship. They follow the active one. So a measure like:

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

will return last-year shipping numbers measured against OrderDate if that's the active relationship, regardless of what you intended. The fix:

Shipped Sales LY = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR('Date'[Date]), USERELATIONSHIP(Sales[ShipDate], 'Date'[Date]) )

USERELATIONSHIP is a CALCULATE filter modifier - it lives at the same level as the SAMEPERIODLASTYEAR call, not nested inside it. It activates the named relationship for the duration of the CALCULATE only.

This is the silent failure mode because nothing errors. The number is plausible. It's just measuring the wrong date.

A diagnostic flow for any broken time intelligence measure

When a time intelligence measure returns the wrong number, work the list in order. Most issues fall in the first three.

1. Is the date column on visuals/slicers from the date dimension, not the fact table? If not, fix that first - nothing else will help.
2. Is the date table marked as a date table? Check Model view; the table icon changes when it is.
3. Does the date table extend into the future? If yes, decide deliberately whether YTD should include future dates. Add an IsPastOrToday column if not.
4. Is the fiscal year correct? If your business year doesn't end 31 December, either pass the "MM-DD" argument or move to fiscal-aware columns.
5. Are there multiple relationships between fact and date? If yes, every time intelligence measure on a non-default relationship needs USERELATIONSHIP.
6. Is the date table contiguous? Gaps break DATESYTD and DATESBETWEEN. Use CALENDAR() or CALENDARAUTO() to generate it; never rely on distinct fact dates.

If all six pass and the measure is still wrong, the issue is filter context interaction - go back to Part 2 and trace what CALCULATE is being asked to do, step by step.

Frequently asked questions

Why does my YTD show the full year value in every month?

Your date table extends into the future and DATESYTD is returning every date from 1 January to 31 December because the filter context isn't bounded. Add an IsPastOrToday column to your date table and filter on it, or wrap the measure with that condition.

Do I need to mark my date table as a date table if I'm using DATESYTD?

Yes. Without it, filters on other date table columns survive the time intelligence call and silently constrain the result. Marking the table makes the engine treat time intelligence functions as if they called ALL('Date') first.

Why does SAMEPERIODLASTYEAR return blank for some periods?

Almost always one of two causes: the visual is sliced on a fact table date column (which doesn't get shifted) instead of the date dimension column, or the date table doesn't include the prior year's dates so the shifted set is empty. Check both.

What's the difference between TOTALYTD and CALCULATE with DATESYTD?

None. TOTALYTD([Measure], 'Date'[Date]) is shorthand for CALCULATE([Measure], DATESYTD('Date'[Date])). Use whichever reads better - the longer form is occasionally clearer when you're combining multiple filter arguments.

How do I do time intelligence on an inactive relationship?

Add USERELATIONSHIP(Fact[DateColumn], 'Date'[Date]) as an additional filter argument inside the same CALCULATE that contains the time intelligence function. It activates the named relationship for that evaluation only.

Should I write my own time intelligence with FILTER instead of using TOTALYTD?

Sometimes. The built-in functions assume a calendar that fits the function's logic. For 4-4-5 retail calendars, ISO weeks, or anything with a non-standard period structure, a fiscal-aware date table with explicit period columns and a CALCULATE that filters on those columns is more reliable than fighting the time intelligence family.

The thing to take from this series

Every confusing DAX result, across all three parts, is the same shape of problem: the engine is doing exactly what it was told, in a context that isn't the one you thought you were in. Row context isn't filter context. CALCULATE bridges them. Time intelligence is CALCULATE with a date table query attached. Once you know which context an expression is evaluating in, and which CALCULATE just modified it, the surprises stop.