Introduction
After the initial euphoria with the new calendar-based time intelligence, I started looking deeper into the new feature to see what these new possibilities mean in the real world.
You will find several links about it in the References section at the end of this piece, including a SQLBI article, which takes you deep into the topic.
I strongly recommend reading these articles to gain a good understanding.
But over time, I realized there are darker sides to this shiny new feature.
Now I will show you four examples, where I discovered interesting effects.
I will offer workarounds or solutions to each issue when possible.
Setup of the Calendars
For this piece, I used two Power BI reports with two Date tables each to avoid interference. All Date tables have the same source table.
A possible interference between Calendars is described here.
For the Gregorian calendar, I used this configuration:
For the Week-based calendar, I used this configuration:
The Weekly calendar includes the YearOfWeek column for the year category.
This column contains the week-aligned year, which is needed for such a calendar. This column is based on the ISO-week definition. Each year starts on Monday of week 1.
You can find an explanation for the ISO week here.
Both Power BI data models used the same configuration.
Previous Months and different month lengths
OK, first, let’s look at months with different lengths.
I describe this case to make you aware of the differences from the classic time-intelligence logic.
I created two measures:
Online Sales (PM) =
CALCULATE([Online Sales]
,DATEADD('Date'[Date], -1, MONTH)
)And this one uses the Gregorian calendar:
Online Sales (PY Gregorian) =
CALCULATE([Online Sales]
,DATEADD('Gregorian Calendar', -1, YEAR)
)I added both to a table visual.
Now look at the differences between these two measures for March:
While this result is very interesting, look at this one:
In both cases, the result is very different.
While the measure using classic time intelligence shows the same value for the last three days of March, the results for February omit the last days of January.
The Calendar-based measure performs much better.
The crucial point here is that the row sums equal the sum shown in the Total row.
Moreover, the DATEADD() function now has two additional parameters that affect results for months with unequal lengths.
While it’s not weird, it’s definitely a different behavior of the function, which you must be aware of. This applies everywhere when periods aren’t of the same length. I will come back to this later.
What happens with the previous year?
Now comes the first weird situation.
Observe the following table using a measure with a DATEADD() call using the Gregorian calendar for PY:
As you can see, everything looks fine.
Now look at the results, when comparing 2024 to 2025:
As you can see, the PY values for March 2025 are shifted by 1 day.
This isn’t correct.
Even worse, when comparing the months’ total values, they are equal between 2024 and the PY measure in 2025.
This effect is observable up to December, where the results are these:
This is the same effect we can observe in the Previous month measure shown earlier, since these two years aren’t the same length.
This weird effect is due to how DAX calculates results based on the calendar hierarchy.
The mechanism is called “Distance from Parent”.
But the Parent is defined by the third parameter of DATEADD(): Year
Therefore, DATEADD() calculates the distance from the beginning of the year and returns the result using the same distance for the previous year.
One solution to this issue is to ensure that all months are of equal length.
In my first article about this new feature, linked in the References section below, I created a custom date table and a calendar with 31 days for all months.
When performing the same operation with that calendar, the effect disappears:
While this approach works flawlessly, it requires a custom calendar, which can cause other issues or fail to cover specific requirements. Especially since the date columns don’t contain real dates, and the date_real column has gaps. This can cause issues when using it in custom calculations.
Another solution is to calculate the PY by moving back by 12 months:
Online Sales (-12 M Gregorian) =
CALCULATE([Online Sales]
,DATEADD('Gregorian Calendar', -12, MONTH)
)And these are the results of the new measure:
In red, you see the same results as before, shifted by one day.
In green, you see the results for the measure with month granularity.
Interestingly, the sums for the quarters and the years are correct as well.
At the moment, I don’t see any issue with using this approach, and I will use and test it in the future.
Weekly calculations – Head scratching
This is a very strange one.
Look at the following picture with the same table in different states side-by-side:
On the left, you see that all rows for 2023 are identical when 2022 is collapsed.
On the right, you see the correct values for 2023, but they are displayed only when I expand at least one week of 2022 up to the Date.
But the values in 2022 are again all the same.
I experienced this already and showed this in my first article about the calendar feature (Link below).
In that instance, I solved it by creating a separate table for the weekly calendar. But this time it didn’t work.
I had to rebuild the data model from scratch, and it worked immediately:
As you can see, the results are correct.
If you look carefully, the PY results are correct to get the PY value of the same week and weekday of the previous year.
I have no clue what the difference is between these two setups.
The Date table is from the same source in both data models, and the calendar is defined by using the same columns.
But I’m a little anxious about this because I don’t understand the reason and don’t have a solution. Even after reviewing the TMDL file for that table, I didn’t find anything that pointed to the cause.
I encountered such an effect only with weekly calculations.
Mixing weekly with monthly logic
One of my clients wants to see a report showing the daily results for the current month, compared with the same week and weekday of the previous year.
This is a mix of the monthly (Gregorian) Calendar with the weekly logic.
As I will show in the next case in more detail, the weekly logic correctly maps the weeks and weekdays to the previous year. Therefore, this should be a problem.
But since the weeks don’t align with the months, I cannot add the Month category. I will get an error when validating when trying to add the Month category.
Therefore, I cannot use an MTD calculation, as the function will not find the needed category:
I cannot add a Gregorian calendar to the same date table, as the engine expects the same column for the same Category for all Calendars on the same table.
See here for Microsoft’s statement about this.
Since I use the YearForWeek column for the Year category, it will not work with the Month category because they do not align.
As a consequence, I had to write custom logic to solve all the requirements.
Weekly calculations – That’s interesting!
To end on a positive note, I can show you something that works very well.
Remember the issue with the months that aren’t of the same length and how the PY values were shifted?
This effect doesn’t appear when performing weekly calculations.
As you can see, the results are correctly calculated based on the week and the correct weekdays.
As expected, the values aren’t mapped to the dates of the previous year but to the weekdays per week.
This is what I expect when observing results by week and weekdays.
The reason is that each week is the same length, and the date table is built to support such a scenario.
Conclusion
As you can see, the results are mixed.
When looking at the results from previous periods of different lengths (months or years), the results shift.
When the periods are of the same length (weeks or the custom calendar), then everything works as expected.
I was extremely surprised and upset when I saw the results for the leap years.
But fortunately, this can be solved by understanding how the new logic works.
The other issue with which I have a bad feeling is the inconsistent functioning of the weekly based calendar and the PY calculation.
This is disturbing, as it’s not always that easy to rebuild a data model.
Another issue I have is that SQLBI reports potential issues when using multiple calendars in the same date table in their article. I have added a link to it below.
This will introduce the need for multiple date tables in the same data model.
Something I’m reluctant to do.
I can imagine this affects multiple visuals in a report, where they use the logic of different calendars but with different categories.
This can be challenging to solve.
But we will see how this feature will evolve, as we are still in Preview.
References
The SQLBI article explaining the Calendar-based time intelligence feature in detail:
https://www.sqlbi.com/articles/introducing-calendar-based-time-intelligence-in-dax
The SQLBI article explaining DATEADD() with the new parameters:
Microsoft’s documentation on the new feature (URL might change over time):
My article with three real-world use cases with the new calendars:
My second article about calendar-based time intelligence and moving average:
A Blog post from Chris Webb about the effects of the calendar-based time intelligence:
Definition of the ISO-Week based on the ISO8601 standard
https://www.calendarz.com/blog/iso-week-numbers-explained-week-1-week-53-and-year-boundaries
Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here.
The Contoso Data can be used freely under the MIT License, as described in this document. I updated the dataset to shift the data to contemporary dates and removed all tables not needed for this example.