What it does

Time intelligence is the set of DAX functions that shift, extend or compare a filter over dates — year to date, same period last year, previous month. Power BI now offers three ways to do it: auto date/time, classic time intelligence over a marked date table, and calendar-based time intelligence, which is in preview as of July 2026.

Key facts

• Auto date/time creates a hidden date table for every date column in the model. Zero setup, hard to manage, and it inflates model size.
• Classic time intelligence needs a dedicated date table marked with Mark as date table, and the functions take a date column reference — SAMEPERIODLASTYEAR('Date'[Date]). It assumes a Gregorian or shifted Gregorian calendar, has no week-based functions, and throws an error if there are missing dates between the first and last date.
• Calendar-based time intelligence (preview) defines calendars as metadata on a table and the functions take the calendar name — TOTALYTD([Sales], 'Fiscal Calendar'). Microsoft's guidance now recommends this approach for best performance and flexibility.
• Calendar-based time intelligence is behind the Enhanced DAX Time Intelligence preview feature in Desktop under File > Options and settings > Options > Preview features, and needs a restart.
• Calendars work with any calendar shape: Gregorian, shifted Gregorian, retail 445/454/544, 13-month, lunar. There's no built-in assumption about structure.
• Calendar-based functions handle sparse dates. Classic functions don't — a gap in the date column is an error.
• Week-granularity functions such as TOTALWTD only exist on the calendar-based side.
• A calendar is defined by assigning columns to categories. Categories are either Complete (enough on their own to identify the period, like Year or Month) or Partial (like Month of Year). Each category has one primary column and any number of associated columns, and you need at least one primary column assigned to save the calendar.
• Marking a date table validates the column: unique values, no nulls, contiguous dates, and the same timestamp on every value if it's a Date/Time type.
• You still have to mark a date table for classic time intelligence, and also when relationships to the date table use integer surrogate keys rather than datetime columns, or when Excel PivotTable advanced date filters are needed.
• Microsoft advises against the older workaround of adding offset columns to date tables to avoid time intelligence functions, except for specific cases — it bloats the model and slows refresh.

When to use / skip

Every model with a date in it needs a date table. Build one, mark it, and stop using auto date/time — that's not really a judgment call. The genuine decision is classic versus calendar-based. Classic is fine and well understood for a straightforward Gregorian or 1-July fiscal year, and it's what every example on the internet uses. Go calendar-based when the client has a retail 445 calendar, a 13-period calendar, week-based reporting, or a date table with deliberate gaps — those cases used to mean hand-rolled offset-column DAX, and now they don't. Bear in mind it's still preview, so weigh that against the client's appetite.

Configuration decisions

• Where the date table comes from: the source system, Power Query with List.Dates, DAX with CALENDAR or CALENDARAUTO, or an external tool. A source-system date table is usually the right answer on any project with a warehouse.
• Whether auto date/time is switched off globally for new files, which it should be.
• Classic or calendar-based, and if calendar-based, whether preview status is acceptable to the client.
• How many calendars the model needs — fiscal and Gregorian side by side is common, and calendar-based supports several on one table.
• Whether the date table extends far enough forward for budgets and forecasts and starts early enough for the oldest fact row.

Gotchas

• A date table that doesn't cover the full range of the fact data breaks classic time intelligence with an error that doesn't obviously point at the date table.
• Auto date/time being on by default means it's already in most client models, quietly. Check model size before and after switching it off — the difference is often startling.
• Marking a date table fails silently in people's understanding when the column is Date/Time with varying timestamps. The validation is real but the reason isn't always obvious.
• Fiscal years that shift the start month are handled by classic time intelligence. Fiscal calendars that restructure weeks and periods are not, and this is where people write hundreds of lines of offset DAX.
• Calendar-based time intelligence changes the screens: Mark as date table moves under Calendar options once you're using it, which is disorienting if you learned the old menu path.
• Preview features are per-machine in Desktop. One developer with it off can't open the model the same way as the developer with it on.

Consultant notes

• Ask about the fiscal calendar in discovery, not during build. "It's just a July start" and "it's a 4-4-5 with a 53rd week every few years" are entirely different projects.
• If the client has a 445 or 13-period calendar, calendar-based time intelligence is worth raising even in preview — the alternative is a pile of bespoke DAX that only you understand.
• Switch off auto date/time in the developer options template and mention it in the handover pack. It's the single easiest model size win on most estates.
• Keep the date table in the warehouse if there is one. Every downstream model then agrees on what a week is, which is a conversation you only want to have once.

---

Calendar-based time intelligence is preview as of July 2026 — check whether it's gone GA before recommending it for a production build.