Introduction
After the initial excitement about 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'll find several links about it in the Reference section at the end of this section, including an article on SQLBI, which takes you deeper into the topic.
I highly recommend reading these articles to get a better understanding.
But later, I realized that there are dark sides to this shiny new feature.
Now I will show you four examples, where I got interesting results.
I will provide workarounds or solutions for each issue if possible.
Setting of Calendars
In this piece, I used two Power BI reports with two Date tables each to avoid clutter. All Date tables have the same source table.
Possible overlaps between Calendars are explained here.
For the Gregorian calendar, I used this setting:
For a week-based calendar, I used this configuration:
The Weekly Calendar includes a YearOfWeek column for the year category.
This column contains the week-aligned year, which is required for such a calendar. This column is based on the definition of ISO week. Each year begins on Monday of week 1.
You can find an explanation of ISO week here.
Both Power BI data models used the same configuration.
Previous months and different month lengths
OK, first, let's look at moons with different lengths.
I explain this case to make you aware of the difference from the old concept of time intelligence.
I created two steps:
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 the table mirror.
Now look at the difference between these two steps of March:
Although this result is very interesting, look at this:
In both cases, the result is very different.
While the old-fashioned average shows the same number for the last three days of March, the results for February leave out the last days of January.
A calendar-based estimate performs best.
The important point here is that the line count equals the sum shown in the Total line.
In addition, the DATEADD() function now has two additional parameters that affect results for months of uneven length.
Although it is not strange, it is definitely a different work behavior, which you should be aware of. This applies everywhere where the periods do not have the same length. I will come back to this later.
What happened last year?
Now comes the first strange situation.
Check the following table using the average with the DATEADD() call using the Gregorian PY calendar:
As you can see, everything looks good.
Now look at the results, comparing 2024 and 2025:
As you can see, PY rates for March 2025 are changed in 1 day.
This is wrong.
Even worse, if you compare the absolute values of the months, they are equal between 2024 and the average PY in 2025.
This result is visible until December, when the results are:
This is the same result we can see in the last month's estimate shown earlier, since the two years are not the same length.
This strange result is caused by the way DAX calculates results based on calendar order.
The method 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 from the previous year.
One solution to this issue is to ensure that all moons are equal in length.
In my first article about this new feature, linked in the References section below, I created a custom date table and calendar with 31 days for every month.
If you do the same activity with that calendar, the effect disappears:
Although this method works flawlessly, it requires a custom calendar, which can cause other problems or fail to cover certain needs. Especially since date columns don't contain actual dates, and the date_real column has spaces. This can cause problems if you use it in custom calculations.
Another solution is to calculate PY going back 12 months:
Online Sales (-12 M Gregorian) =
CALCULATE([Online Sales]
,DATEADD('Gregorian Calendar', -12, MONTH)
)And here are the results of the new measurement:
In red, you see the same results as before, changed in one day.
In green, you see the results of the measurement at the granularity of the month.
Interestingly, the figures for quarters and years are also correct.
At the moment, I don't see any problem using this method, and I will use it and test it in the future.
Weekly stats – Head scratching
This is very strange.
Take a look at the following image with the same table in different states facing each other:
On the left, you see that all the lines of 2023 are the same when 2022 is folded.
On the right, you see the correct values for 2023, but they are only shown if I extend at least one week of 2022 until the date.
But the rates in 2022 are again the same.
I encountered this already and showed this in my first article about the calendar feature (Link below).
In that example, I solved it by creating a separate weekly calendar table. But this time it didn't work.
I had to rebuild the data model from scratch, and it worked quickly:
As you can see, the results are good.
If you look carefully, the PY results are correct so that you can get the PY value of the same week as the day of the week of the previous year.
I don't know what the difference is between the two programs.
The Date table comes from the same source in both data models, and the calendar is defined using the same columns.
But I'm worried about this because I don't understand the reason and I don't have a solution. Even after reviewing the TMDL file for that table, I couldn't find anything that pointed to the cause.
I have experienced such a result only with weekly statistics.
Combining weekly and monthly logic
One of my clients wants to see a report that shows the daily results for the current month, compared to the same week and the midweek day of the previous year.
This is a combination of a monthly (Gregorian) Calendar with a weekly concept.
As I will show in the next article in detail, the weekly logic correctly sets the weeks and days of the previous year. So, this should be a problem.
But since weeks don't match months, I can't add a Moon category. I will get an error when verifying when I try to add the Moon category.
Therefore, I cannot use the MTD calculation, since the function will not get the required phase:
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 in the same table.
See here for Microsoft's statement on this.
Since I'm using the YearForWeek column in the Year section, it won't work with the Month section because they don't match.
As a result, I had to write custom logic to solve all the requirements.
Weekly stats – That's interesting!
To end on a positive note, I can show you something that works very well.
Remember the issue of unequal length months and how the PY values are changed?
This effect does not appear when doing weekly calculations.
As you can see, the results are calculated correctly based on the week and the relevant days of the week.
As expected, the values are not mapped to dates of the previous year but week to week.
This is what I expect when I look at results by week and day of the week.
The reason is that each week has the same length, and the date table is designed to support such a situation.
The conclusion
As you can see, the results are mixed.
If you look at the results from previous periods of different lengths (months or years), the results change.
If the sessions have the same length (weeks or custom calendar), then everything works as expected.
I was very surprised and angry when I saw the results of the leap years.
But luckily, this can be solved by understanding how the new logic works.
Another issue I feel bad about is the inconsistent performance of the weekly based calendar and PY calculation.
This is disturbing, as it is not that easy to reconstruct the data model.
Another problem I have is that SQLBI reports potential problems when using multiple calendars on the same date table in their article. I've 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 that this affects a lot of visuals in the report, where they use the understanding of different calendars but in different categories.
This can be a challenge to solve.
But we will see how this feature will develop, as it is still in preview.
References
SQLBI article that describes the Calendar-based intelligence feature in detail:
SQLBI article that defines DATEADD() with new parameters:
Microsoft documentation on the new feature (URL may change over time):
My article with three real-world use cases with the new calendars:
My second article on calendar-based time intelligence and the moving average:
A blog post from Chris Webb about the effects of calendar-based time intelligence:
Definition of ISO-Week based on ISO8601 standard
As in my previous articles, I am using the Contoso sample data. You can download the ContosoRetailDW Dataset for free from Microsoft here.
Contoso data can be freely used under the MIT license, as described in this document. I updated the dataset to convert the data to modern dates and remove all the tables that are not needed for this example.
