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,STARTOFWEEKand 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:
| Approach | Setup | Flexibility | Notes |
|---|---|---|---|
| Auto date/time | Almost none | Low | Creates hidden date tables and grows the model |
| Classic time intelligence | Medium | Low | Needs a date table; assumes Gregorian or shifted Gregorian |
| Calendar-based (preview) | High | High | Recommended; 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:
- Use New calendar and give it a name that’s unique in the model, for example
Retail 445. - Use Add category to assign columns to categories.
- 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):
DATEADDandSAMEPERIODLASTYEAR. 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:
extensionwhen shifting to a longer period:precise,extended,endalignedtruncationwhen shifting to a shorter one:anchored,blankendbehaviorforDATESINPERIOD: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:
- Measures get simpler. The long patterns I used to write for last-year comparisons on a 4-4-5 calendar shrink to one line.
- 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
- Open your date table and list the calendars your business actually uses: Gregorian, fiscal, retail, ISO week.
- Add the columns each one needs and make periods unique (Week on its own, or Week of Year + Year).
- In a test file, enable Enhanced DAX Time Intelligence and define a single calendar.
- Put three or four of your classic measures next to their calendar-based versions and compare the numbers.
- 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.
