I saw the September 2025 update for Custom Calendars in Power BI and honestly thought we were moving toward a world where time intelligence didn’t require a blood sacrifice. Specifically, the promise of built-in weekly and custom logic in Tabular models looked like a massive win for efficiency. However, if you’ve spent any time debugging a production model during a leap year, you know that “Preview” features often come with teeth. When things get weird in DAX, they don’t just fail; they drift silently.
In this post, I want to break down some of the specific “gotchas” I’ve seen when implementing these new calendar-based features. We aren’t just talking about syntax errors here. We are talking about architectural quirks that can make your Year-Over-Year (YOY) reports look like they were calculated by a broken clock. If you want to build a truly robust like-for-like Power BI model, you need to understand these pitfalls before you ship them.
The Leap Year Shift in Custom Calendars
The first major “weirdness” happens during leap years. When you use the DATEADD function with a Custom Calendar, DAX employs a mechanism called “Distance from Parent.” Therefore, if your Parent is defined as the Year, DAX calculates the distance from the start of the year and attempts to replicate that distance in the previous year.
Consequently, when comparing 2024 (a leap year) to 2025, your PY values will shift by exactly one day starting in March. It’s a subtle bug that ruins the row-level integrity of your visuals. Here is the typical naive approach that causes this:
Online Sales (PY Gregorian) =
CALCULATE(
[Online Sales],
DATEADD('Gregorian Calendar', -1, YEAR)
)This looks correct, but because of the year-length difference, March 2025 values end up mapped to the wrong day in 2024. Furthermore, by December, you’ll find DAX summing up the last two days of the year into a single row because it’s trying to reconcile the unequal lengths. Specifically, the drift happens because the “parent” (Year) has changed in size.
The Fix: Month Granularity Over Year
Surprisingly, the solution isn’t to build a massive custom table (though that works). Instead, you can refactor your DATEADD logic to move back by months rather than years. By shifting the granularity to 12 months, you bypass the “Distance from Parent” quirk that breaks during leap year transitions. Check out the corrected measure below:
Online Sales (-12 M Gregorian) =
CALCULATE(
[Online Sales],
DATEADD('Gregorian Calendar', -12, MONTH)
)In my tests, this approach corrected the 1-day drift and kept the quarter and year totals accurate. It is much cleaner than the “war story” fix of creating a custom calendar with 31 days for every month just to force consistency.
Weekly Logic and the “Consistent Categorization” Error
Another bottleneck occurs when you try to mix weekly logic with monthly reporting. Clients often want to see daily results for the current month compared to the same week and weekday of the previous year. However, because weeks don’t align perfectly with months, you cannot simply add a Month category to a Weekly calendar table.
Microsoft’s engine expects consistent categorization across all calendars on the same table. If you try to force a DATESMTD() call on a calendar that lacks a month category, you’ll hit a validation error. You can find more about this in my guide on optimizing DAX filtering to avoid killing your model’s performance when dealing with these complex filters.
Look, if this Custom Calendars stuff is eating up your dev hours, let me handle it. I’ve been wrestling with WordPress and enterprise data integrations since the 4.x days, and I know exactly where the bodies are buried in these preview features.
Final Takeaway on Tabular Time Intelligence
The new Custom Calendars in Power BI and Fabric are a massive step forward, but they aren’t “set it and forget it.” When periods are of equal length (like weeks), the logic holds up beautifully. When they aren’t (like Gregorian months and leap years), you have to be the architect, not just the user. Refactor your DATEADD parameters, watch your parent-child distances, and always validate your totals against a raw date-for-date check before you ship it to the client.
