The BI community has mostly settled on the idea that built-in calendar-based time intelligence made custom logic obsolete. For a standard Gregorian model that is close enough to true. For complex financial models or a non-standard fiscal year, leaning only on the out-of-the-box functions is how you end up with a report nobody wants to open.
Fourteen years of data architecture work has shown me the same trap over and over. Someone swaps a date column for a calendar table inside a DATESINPERIOD call, decides the problem is handled, and never looks at the execution plan. The dashboard is quick during testing and crawls once the fact table hits a few million rows.
The standard pattern, and where it stops
With a standard Gregorian calendar, calendar-based time intelligence is straightforward. The usual pattern for a moving average looks like this:
Running Average by Month =
VAR MaxDate = MAX( 'Date'[Date] )
VAR DateRange =
DATESINPERIOD(
'Date'[Date],
MaxDate,
-3,
MONTH
)
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Date'[MonthKey],
"#Sales", [Sum Online Sales]
),
DateRange
)
RETURN
AVERAGEX(SalesByMonth, [#Sales])That code is fine, and it stays fine right up to the point where a month is not a month. Enterprise models run on 4-4-5 calendars and 15-month financial years, and DATESINPERIOD has nothing to hook into once there is no standard date column underneath it.
The structure of the model decides most of your DAX performance, which I went into in more detail in building enterprise-grade financial models in Power BI.
Making the custom logic fast
When the standard DATESINPERIOD function does not apply, the usual fallback is brute force: grab the min and max IDs, filter the whole table, and watch the memory footprint balloon. I reach for a day index or row rank instead. Give every row in the calendar a sequential integer and the date logic collapses into integer comparisons.
The choice between TOPN and a plain range filter on a RowRank column changes execution time more than you would expect. On a recent refactor, a client’s moving average went from 500ms to 110ms, and the only change was dropping the MAX/MIN ID logic for a precalculated rank.
// Optimized Version using RowRank
Running Average (Financial) =
VAR MaxDateRank = MAX('Financial Calendar'[ID_Date_RowRank])
VAR DateRange =
CALCULATETABLE(
SUMMARIZECOLUMNS('Financial Calendar'[ID_Date]),
REMOVEFILTERS('Financial Calendar'),
'Financial Calendar'[ID_Date_RowRank] <= MaxDateRank &&
'Financial Calendar'[ID_Date_RowRank] >= MaxDateRank - 92
)
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Financial Calendar'[ID_Month],
"#Sales", [Sum Online Sales]
),
DateRange
)
RETURN
AVERAGEX(SalesByMonth, [#Sales])Where it does help: weekly intervals
Weekly intervals are the case where calendar-based time intelligence earns its keep. Rolling weeks in DAX used to mean a pile of manual filtering, and now you pass WEEK as a parameter and move on. Semesters and other custom buckets still need the manual work, so the convenience only covers part of the job.
If DAX like this is eating your dev hours, I can take it on. I have been working with WordPress and enterprise data architecture since the 4.x days.
The calendar table matters more
Picking the newest DAX function matters less than having a consistent calendar table. Classic pattern or new calendar features, the logic is only ever as good as the data under it. A messy calendar produces wrong totals, and no amount of AVERAGEX tuning will fix that for you.
