Calendar-based time intelligence (preview)

Last checked against Microsoft documentation: 14 November 2026

Calendar-based time intelligence lets you define any calendar inside the model (fiscal, retail 4-4-5, ISO, 13-month, lunar) and pass its name to functions such as TOTALYTD, so you stop building offset columns to fake it.

Status: preview. The Microsoft Learn page still labels it preview and says its performance numbers are not representative of the final product. It also says Microsoft recommends this approach for most new models. Turn it on under File > Options and settings > Options > Preview features > Enhanced DAX Time Intelligence, then restart Desktop.

Why it matters

ClassicCalendar-based (preview)
SetupEasy: mark a date tableMore work: define calendars and categories
Calendar typesGregorian or shifted GregorianAny calendar
Missing datesErrorAllowed
Week-based totalsNot supportedSupported (TOTALWTD)
PerformanceCan be poor in some casesGenerally better
StatusStablePreview

Step by step: define a calendar

  1. Right-click your date table (or use the Table tools ribbon) and choose Calendar options.
  2. Select New calendar and give it a name that is unique in the model.
  3. Select Add category, pick a category (Year, Quarter, Month, Week, Date and so on), and choose its primary column. You may add associated columns, such as a month name.
  4. Repeat for each category. A calendar needs at least one primary column, and a column can sit in only one category per calendar.
  5. Select Validate data (optional but recommended) and fix any warnings. Real-time validation errors must pass before you can save.
  6. Save. The calendar now appears under the date table in the model explorer.

You can also write calendars in TMDL. Columns tagged only as time-related can be set only this way, or with an external tool:

TMDL
calendar 'Demo Calendar'
    calendarColumnGroup = year
        primaryColumn: Year
    calendarColumnGroup = month
        primaryColumn: Month
        associatedColumn: MonthName
    calendarColumnGroup
        column: isWorkingDay

Use it in DAX

The functions take the calendar's name instead of a date column:

DAX
-- Year to date on a fiscal calendar
Sales YTD = TOTALYTD ( [Sales], 'Fiscal Calendar' )

-- Week to date (new: week-level totals)
Sales WTD = TOTALWTD ( [Sales], 'Fiscal Calendar' )

-- Month to date on an ISO 4-5-4 calendar
Quantity MTD = TOTALMTD ( [Total Quantity], 'ISO-454' )

Each function needs the right categories. TOTALWTD needs a week set (Week, or Week of Year plus Year). Quarter calculations need Quarter, or Quarter of Year plus Year. If a category is missing, the measure returns an error.

Nesting calendar functions is not supported. Apply the inner one through CALCULATETABLE:

DAX
-- Not supported
PREVIOUSDAY ( PREVIOUSMONTH ( 'Calendar' ) )

-- Works
CALCULATETABLE ( PREVIOUSDAY ( 'Calendar' ), PREVIOUSMONTH ( 'Calendar' ) )

Two behaviours worth knowing

  • Sparse dates are fine. Classic functions fail when the date column has gaps. Calendar-based functions work on the dates as they are, so if a shop is closed on weekends, PREVIOUSDAY can jump from Monday to Friday.
  • Context clearing. When a function shifts time on one category, it clears filters on the related categories (both primary and associated columns). Shifting a quarter with DATEADD clears Year and Month of Year, so last quarter's total does not clash with the row's own month filter. Most functions also clear time-related columns. DATEADD and SAMEPERIODLASTYEAR do not. To keep a time-related column filtered, wrap it in VALUES.

Watch-outs

  • You cannot author calendars in the Power BI service yet.
  • Calendars do not work with live connections or composite models.
  • Do not mix auto date/time tables with custom calendars.
  • Time intelligence on a fact table that defines a calendar, combined with row-level security, can give unexpected results.
  • The table holding the calendar must have fewer than 200 columns. A calendar can only use columns from its own table.
  • Validation is not available when the table has more than 20,000 rows, although you can still add the calendar.
  • Associate only the columns you actually use. Avoid adding offset columns to the date table: Microsoft says they inflate the model and slow refreshes.

Best practices

  1. Use calendar-based for non-Gregorian calendars, week-based reporting, sparse dates, or when classic performance is poor. Keep classic for a simple Gregorian model with a complete date table.
  2. Test every measure at year, quarter, month and week level before you trust it.
  3. Because it is preview, keep a classic version of critical measures until the status changes.

Official sources