Skip to content
TREN

Goodbye to Classic Time Intelligence? A Guide to Calendar-Based Time Intelligence in DAX

Microsoft now recommends calendar-based time intelligence over the classic functions for most models. What changes for 4-4-5 retail calendars, ISO weeks and fiscal years, how to set it up, and where it can trip you up.

Goodbye to Classic Time Intelligence? A Guide to Calendar-Based Time Intelligence in DAX
In this post 9
  1. In short
  2. Three approaches, one recommendation
  3. Defining a calendar
  4. What changes in DAX
  5. Filter clearing: the easiest thing to miss
  6. Limitations
  7. My take
  8. Where to start
  9. Sources

Author: @atilayberber

For years, “same period last year” in Power BI rested on one assumption: the year starts on January 1 and a month is a calendar month. A company with an April fiscal year could get by with a parameter. But once you needed a 4-4-5 retail calendar, ISO weeks or a 13-period year, you were back to writing long CALCULATE + FILTER patterns by hand.

Calendar-based time intelligence removes that assumption. It’s still in preview, but Microsoft Learn is now explicit: this is the recommended approach for most models. The classic functions keep working, but the direction is clear.

In this post I’ll cover how to define a calendar, how the functions behave, and where you still need to be careful. I wrote about why you need a date table and how to build one in Why Your Power BI Model Needs a Date Table; this is the next floor up.

In short

  • A calendar is a model object that maps columns in your date table to categories such as year, quarter, month, week and day.
  • Time intelligence functions can now take a calendar name instead of a date column: TOTALYTD ( [Sales], 'Fiscal Calendar' ).
  • Beyond Gregorian and shifted Gregorian, you can define 4-4-5 / 4-5-4 / 5-4-4 retail, 13-month and lunar calendars.
  • There’s a family of week functions: TOTALWTD, PREVIOUSWEEK, STARTOFWEEK and more.
  • Status: preview. You turn it on under Preview features in Power BI Desktop. Calendars can’t be authored in the service yet, and they don’t work with live connections or composite models.

Three approaches, one recommendation

Microsoft Learn lays out three ways to handle time calculations:

ApproachSetupFlexibilityNotes
Auto date/timeAlmost noneLowCreates hidden date tables and grows the model
Classic time intelligenceMediumLowNeeds a date table; assumes Gregorian or shifted Gregorian
Calendar-based (preview)HighHighRecommended; supports week-based and non-Gregorian calendars

The limit of the classic functions is that they only know the date column. They have no idea how your year is divided. Calendar-based functions read the tagged columns in the table and get the answer to “what does a year, a quarter or a week mean in this model?” from there.

Defining a calendar

Turn the feature on first: File › Options and settings › Options › Preview features › Enhanced DAX Time Intelligence, then restart Desktop.

Right-click your date table (or use the Table tools ribbon) and open Calendar options:

  1. Use New calendar and give it a name that’s unique in the model, for example Retail 445.
  2. Use Add category to assign columns to categories.
  3. Pick one primary column per category. Other columns with the same meaning (a month name, say) go in as associated columns.

Categories come in two kinds:

  • Complete: the value identifies a period on its own. Year, Quarter, Month, Week, Date. For example, “2026-W45”.
  • Partial: only meaningful with a parent period. Month of Year, Week of Year, Day of Week and so on. “W45” doesn’t tell you which year.

Real-time validations run before you can save. The one that matters most is period uniqueness: for weekly calculations you need either a complete Week column or a combination like Week of Year + Year. Validate data adds offline checks for blanks and cardinality; you can save with warnings, but you should fix them.

You can also define calendars in TMDL. Time-related columns such as a holiday name or a working-day flag can only be tagged that way (or with external tools) for now:

table Date
    calendar 'Retail 445'
        calendarColumnGroup = year
            primaryColumn: 'Retail Year'

        calendarColumnGroup = month
            primaryColumn: 'Retail Period'
            associatedColumn: 'Retail Period Name'

        calendarColumnGroup = week
            primaryColumn: 'Retail Week'

        calendarColumnGroup
            column: 'Holiday Name'
            column: IsWorkingDay

One table can hold several calendars: Gregorian, 4-4-5 and fiscal on the same date table. The only rule is that a column keeps the same category in every calendar.

What changes in DAX

Functions take the calendar name instead of a date column:

Sales YTD (Fiscal) =
TOTALYTD ( [Sales], 'Fiscal Calendar' )

Sales MTD (445) =
TOTALMTD ( [Sales], 'Retail 445' )

Sales LY (445) =
CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Retail 445' ) )

Sales WTD =
TOTALWTD ( [Sales], 'Retail 445' )

Week-over-week comparison is bread and butter in retail, so to me the week functions are the most valuable part of this: TOTALWTD, PREVIOUSWEEK, NEXTWEEK, STARTOFWEEK, ENDOFWEEK, OPENINGBALANCEWEEK and CLOSINGBALANCEWEEK. They arrived in September 2025 and require a calendar; classic time intelligence doesn’t support week-based calculations at all.

Lateral and hierarchical shifts

The functions fall into two groups:

  • Fixed (lateral only): DATEADD and SAMEPERIODLASTYEAR. They shift the context but never return values from a different level of detail. February goes to last year’s February.
  • Flexible (hierarchical): every other time intelligence function. Depending on the calendar, they can move across levels, for example from a month to the start of the year.

For year shifts, the functions use a “same distance from the parent” rule: a period keeps its position within the year. In a 4-4-5 calendar, moving between five-week and four-week periods makes that rule matter, so check the results by hand once.

New DATEADD parameters

When shifting between periods of different length, you can pick the behavior:

  • extension when shifting to a longer period: precise, extended, endaligned
  • truncation when shifting to a shorter one: anchored, blank
  • endbehavior for DATESINPERIOD: precise, endaligned

A small warning: IntelliSense doesn’t recognize these parameters yet. A red squiggle doesn’t necessarily mean you got it wrong.

Sparse dates are fine

Classic functions need a table with no gaps between the first and last date. Calendar-based ones don’t. In a table with no weekend rows, PREVIOUSDAY can jump from Monday to Friday.

No nesting

-- Not supported
Wrong = PREVIOUSDAY ( PREVIOUSMONTH ( 'Calendar' ) )

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

Filter clearing: the easiest thing to miss

Calendar-based functions don’t clear the filter on the whole date table; they clear the columns tagged in the calendar. When a function shifts on a category, it also clears the categories that depend on it and that it depends on. A quarter-level DATEADD also clears Year and Month of Year, for example.

Time-related columns behave differently by function. Most functions clear filters on them; DATEADD and SAMEPERIODLASTYEAR keep them. So in a visual filtered to working days only, the last-year comparison still compares working days, but a year-to-date total drops that filter.

SQLBI adds an important warning: filter clearing applies across every calendar on the table. With several calendars on one table, an unrelated calendar’s columns can get cleared too. If you define more than one calendar, test your measures by checking which filters actually survive.

Limitations

  • Calendars can’t be authored in the Power BI service yet.
  • No support for live connections or composite models.
  • Don’t combine auto date/time with custom calendars.
  • Only columns from the calendar’s own table can be categorized, and each column maps to one category.
  • The table must have fewer than 200 columns. Validations don’t run on tables over 20,000 rows, though you can still add a calendar.
  • Row-level security on a fact table that defines a calendar can produce unexpected results.
  • Microsoft says preview performance isn’t representative of the final product.

My take

This turns “time intelligence functions” into “a model that understands time.” The logic moves out of DAX measures and into the model’s metadata. That has two consequences:

  1. Measures get simpler. The long patterns I used to write for last-year comparisons on a 4-4-5 calendar shrink to one line.
  2. Mistakes move into metadata too. One mislabeled column quietly skews every measure that uses the calendar. Calendar definitions now deserve a code review.

My advice: don’t move critical reports before the preview ends. But if you’re starting a new model, especially with a retail or fiscal calendar, design the calendar now. Add the columns (Retail Year, Retail Period, Retail Week, Week of Year) to your date table today, so that when GA arrives, switching over is just a tagging exercise.

Where to start

  1. Open your date table and list the calendars your business actually uses: Gregorian, fiscal, retail, ISO week.
  2. Add the columns each one needs and make periods unique (Week on its own, or Week of Year + Year).
  3. In a test file, enable Enhanced DAX Time Intelligence and define a single calendar.
  4. Put three or four of your classic measures next to their calendar-based versions and compare the numbers.
  5. If you define more than one calendar, test filter clearing specifically.

How does your company split the year: calendar year, fiscal year or 4-4-5? Tell me on LinkedIn.

Sources

If you found it useful, share it on LinkedIn and ask your questions there.

Read next Next in the series · Power BI & DAX Guides · 6 minText Search in DAX Just Got Real: TEXTCONTAINS, TEXTSIMILARITY and String Indexing Same topic · DAX · 6 minCan You Live with 1.6% Error? APPROXIMATEDISTINCTCOUNT Comes to Import and Direct Lake

Leave a comment

Your email address will not be published. Required fields are marked *

STAY IN THE LOOP

New guides, sorted by topic in a single email.

Every Monday, the week's new posts arrive grouped by topic. No new posts, no email.

Nothing is sent until you click the link in the confirmation email. Unsubscribe anytime with one click.