Introduction
After the preliminary euphoria with the brand new calendar-based time intelligence, I began wanting deeper into the brand new characteristic to see what these new potentialities imply in the actual world.
One can find a number of hyperlinks about it within the References part on the finish of this piece, together with a SQLBI article, which takes you deep into the subject.
I strongly suggest studying these articles to achieve an excellent understanding.
However over time, I spotted there are darker sides to this shiny new characteristic.
Now I’ll present you 4 examples, the place I found attention-grabbing results.
I’ll supply workarounds or options to every challenge when potential.
Setup of the Calendars
For this piece, I used two Energy BI stories with two Date tables every to keep away from interference. All Date tables have the identical supply desk.
A potential 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 consists of the YearOfWeek column for the 12 months class.
This column incorporates the week-aligned 12 months, which is required for such a calendar. This column is predicated on the ISO-week definition. Annually begins on Monday of week 1.
You will discover a proof for the ISO week here.
Each Energy BI information fashions used the identical configuration.
Earlier Months and totally different month lengths
OK, first, let’s take a look at months with totally different lengths.
I describe this case to make you conscious of the variations from the basic time-intelligence logic.
I created two measures:
On-line Gross sales (PM) =
CALCULATE([Online Sales]
,DATEADD('Date'[Date], -1, MONTH)
)And this one makes use of the Gregorian calendar:
On-line Gross sales (PY Gregorian) =
CALCULATE([Online Sales]
,DATEADD('Gregorian Calendar', -1, YEAR)
)I added each to a desk visible.
Now take a look at the variations between these two measures for March:
Whereas this end result could be very attention-grabbing, take a look at this one:
In each instances, the end result could be very totally different.
Whereas the measure utilizing basic time intelligence exhibits the identical worth for the final three days of March, the outcomes for February omit the final days of January.
The Calendar-based measure performs significantly better.
The essential level right here is that the row sums equal the sum proven within the Whole row.
Furthermore, the DATEADD() operate now has two further parameters that have an effect on outcomes for months with unequal lengths.
Whereas it’s not bizarre, it’s undoubtedly a special habits of the operate, which you have to pay attention to. This is applicable in every single place when durations aren’t of the identical size. I’ll come again to this later.
What occurs with the earlier 12 months?
Now comes the primary bizarre scenario.
Observe the next desk utilizing a measure with a DATEADD() name utilizing the Gregorian calendar for PY:
As you’ll be able to see, every little thing seems to be effective.
Now take a look at the outcomes, when evaluating 2024 to 2025:
As you’ll be able to see, the PY values for March 2025 are shifted by 1 day.
This isn’t right.
Even worse, when evaluating the months’ whole values, they’re equal between 2024 and the PY measure in 2025.
This impact is observable as much as December, the place the outcomes are these:
This is similar impact we will observe within the Earlier month measure proven earlier, since these two years aren’t the identical size.
This bizarre impact is because of how DAX calculates outcomes based mostly on the calendar hierarchy.
The mechanism known as “Distance from Dad or mum”.
However the Dad or mum is outlined by the third parameter of DATEADD(): 12 months
Subsequently, DATEADD() calculates the space from the start of the 12 months and returns the end result utilizing the identical distance for the earlier 12 months.
One answer to this challenge is to make sure that all months are of equal size.
In my first article about this new characteristic, linked within the References part beneath, I created a customized date desk and a calendar with 31 days for all months.
When performing the identical operation with that calendar, the impact disappears:
Whereas this strategy works flawlessly, it requires a customized calendar, which might trigger different points or fail to cowl particular necessities. Particularly because the date columns don’t include actual dates, and the date_real column has gaps. This may trigger points when utilizing it in customized calculations.
One other answer is to calculate the PY by shifting again by 12 months:
On-line Gross sales (-12 M Gregorian) =
CALCULATE([Online Sales]
,DATEADD('Gregorian Calendar', -12, MONTH)
)And these are the outcomes of the brand new measure:
In purple, you see the identical outcomes as earlier than, shifted by sooner or later.
In inexperienced, you see the outcomes for the measure with month granularity.
Apparently, the sums for the quarters and the years are right as effectively.
In the mean time, I don’t see any challenge with utilizing this strategy, and I’ll use and check it sooner or later.
Weekly calculations – Head scratching
It is a very unusual one.
Take a look at the next image with the identical desk in numerous states side-by-side:
On the left, you see that each one rows for 2023 are similar when 2022 is collapsed.
On the fitting, you see the proper values for 2023, however they’re displayed solely after I increase a minimum of one week of 2022 as much as the Date.
However the values in 2022 are once more all the identical.
I skilled this already and confirmed this in my first article in regards to the calendar characteristic (Hyperlink beneath).
In that occasion, I solved it by making a separate desk for the weekly calendar. However this time it didn’t work.
I needed to rebuild the information mannequin from scratch, and it labored instantly:
As you’ll be able to see, the outcomes are right.
In case you look fastidiously, the PY outcomes are right to get the PY worth of the identical week and weekday of the earlier 12 months.
I’ve no clue what the distinction is between these two setups.
The Date desk is from the identical supply in each information fashions, and the calendar is outlined by utilizing the identical columns.
However I’m somewhat anxious about this as a result of I don’t perceive the explanation and don’t have an answer. Even after reviewing the TMDL file for that desk, I didn’t discover something that pointed to the trigger.
I encountered such an impact solely with weekly calculations.
Mixing weekly with month-to-month logic
One in all my purchasers needs to see a report exhibiting the day by day outcomes for the present month, in contrast with the identical week and weekday of the earlier 12 months.
It is a mixture of the month-to-month (Gregorian) Calendar with the weekly logic.
As I’ll present within the subsequent case in additional element, the weekly logic appropriately maps the weeks and weekdays to the earlier 12 months. Subsequently, this needs to be an issue.
However because the weeks don’t align with the months, I can not add the Month class. I’ll get an error when validating when attempting so as to add the Month class.
Subsequently, I can not use an MTD calculation, because the operate is not going to discover the wanted class:
I can not add a Gregorian calendar to the identical date desk, because the engine expects the identical column for a similar Class for all Calendars on the identical desk.
See here for Microsoft’s assertion about this.
Since I take advantage of the YearForWeek column for the 12 months class, it is not going to work with the Month class as a result of they don’t align.
As a consequence, I needed to write customized logic to unravel all the necessities.
Weekly calculations – That’s attention-grabbing!
To finish on a optimistic observe, I can present you one thing that works very effectively.
Bear in mind the problem with the months that aren’t of the identical size and the way the PY values had been shifted?
This impact doesn’t seem when performing weekly calculations.
As you’ll be able to see, the outcomes are appropriately calculated based mostly on the week and the proper weekdays.
As anticipated, the values aren’t mapped to the dates of the earlier 12 months however to the weekdays per week.
That is what I count on when observing outcomes by week and weekdays.
The reason being that every week is similar size, and the date desk is constructed to help such a state of affairs.
Conclusion
As you’ll be able to see, the outcomes are blended.
When wanting on the outcomes from earlier durations of various lengths (months or years), the outcomes shift.
When the durations are of the identical size (weeks or the customized calendar), then every little thing works as anticipated.
I used to be extraordinarily stunned and upset after I noticed the outcomes for the leap years.
However happily, this may be solved by understanding how the brand new logic works.
The opposite challenge with which I’ve a nasty feeling is the inconsistent functioning of the weekly based mostly calendar and the PY calculation.
That is disturbing, because it’s not at all times that simple to rebuild a knowledge mannequin.
One other challenge I’ve is that SQLBI stories potential points when utilizing a number of calendars in the identical date desk of their article. I’ve added a hyperlink to it beneath.
It will introduce the necessity for a number of date tables in the identical information mannequin.
One thing I’m reluctant to do.
I can think about this impacts a number of visuals in a report, the place they use the logic of various calendars however with totally different classes.
This may be difficult to unravel.
However we are going to see how this characteristic will evolve, as we’re nonetheless in Preview.
References
The SQLBI article explaining the Calendar-based time intelligence characteristic intimately:
https://www.sqlbi.com/articles/introducing-calendar-based-time-intelligence-in-dax
The SQLBI article explaining DATEADD() with the brand new parameters:
Microsoft’s documentation on the brand new characteristic (URL may change over time):
My article with three real-world use instances with the brand new calendars:
My second article about calendar-based time intelligence and shifting common:
A Weblog submit from Chris Webb in regards to the results of the calendar-based time intelligence:
Definition of the ISO-Week based mostly on the ISO8601 customary
https://www.calendarz.com/blog/iso-week-numbers-explained-week-1-week-53-and-year-boundaries
Like in my earlier articles, I take advantage of the Contoso pattern dataset. You’ll be able to obtain the ContosoRetailDW Dataset without cost from Microsoft here.
The Contoso Knowledge can be utilized freely below the MIT License, as described in this document. I up to date the dataset to shift the information to up to date dates and eliminated all tables not wanted for this instance.
