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
| Classic | Calendar-based (preview) | |
|---|---|---|
| Setup | Easy: mark a date table | More work: define calendars and categories |
| Calendar types | Gregorian or shifted Gregorian | Any calendar |
| Missing dates | Error | Allowed |
| Week-based totals | Not supported | Supported (TOTALWTD) |
| Performance | Can be poor in some cases | Generally better |
| Status | Stable | Preview |
Step by step: define a calendar
- Right-click your date table (or use the Table tools ribbon) and choose Calendar options.
- Select New calendar and give it a name that is unique in the model.
- 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.
- Repeat for each category. A calendar needs at least one primary column, and a column can sit in only one category per calendar.
- Select Validate data (optional but recommended) and fix any warnings. Real-time validation errors must pass before you can save.
- 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:
calendar 'Demo Calendar'
calendarColumnGroup = year
primaryColumn: Year
calendarColumnGroup = month
primaryColumn: Month
associatedColumn: MonthName
calendarColumnGroup
column: isWorkingDayUse it in DAX
The functions take the calendar's name instead of a date column:
-- 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:
-- 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,
PREVIOUSDAYcan 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
DATEADDclears 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.DATEADDandSAMEPERIODLASTYEARdo not. To keep a time-related column filtered, wrap it inVALUES.
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
- 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.
- Test every measure at year, quarter, month and week level before you trust it.
- Because it is preview, keep a classic version of critical measures until the status changes.