Introduction
After the preliminary euphoria with the brand new calendar-based time intelligence, I began trying deeper into the brand new characteristic to see what these new potentialities imply in the actual world.
You can see 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 advocate studying these articles to achieve a great understanding.
However over time, I noticed there are darker sides to this shiny new characteristic.
Now I’ll present you 4 examples, the place I found fascinating results.
I’ll provide workarounds or options to every challenge when doable.
Setup of the Calendars
For this piece, I used two Energy BI reviews with two Date tables every to keep away from interference. All Date tables have the identical supply desk.
A doable interference between Calendars is described right 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 yr class.
This column comprises the week-aligned yr, which is required for such a calendar. This column relies on the ISO-week definition. Every year begins on Monday of week 1.
Yow will discover an evidence for the ISO week right here.
Each Energy BI information fashions used the identical configuration.
Earlier Months and totally different month lengths
OK, first, let’s have 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 have a look at the variations between these two measures for March:
Whereas this end result could be very fascinating, have 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 a lot 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 extra parameters that have an effect on outcomes for months with unequal lengths.
Whereas it’s not bizarre, it’s undoubtedly a unique habits of the operate, which you need to pay attention to. This is applicable in all places when intervals aren’t of the identical size. I’ll come again to this later.
What occurs with the earlier yr?
Now comes the primary bizarre state of affairs.
Observe the next desk utilizing a measure with a DATEADD() name utilizing the Gregorian calendar for PY:
As you may see, all the pieces appears to be like nice.
Now have a look at the outcomes, when evaluating 2024 to 2025:
As you may see, the PY values for March 2025 are shifted by 1 day.
This isn’t right.
Even worse, when evaluating the months’ complete 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 are able to 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 is known as “Distance from Mum or dad”.
However the Mum or dad is outlined by the third parameter of DATEADD(): Yr
Subsequently, DATEADD() calculates the space from the start of the yr and returns the end result utilizing the identical distance for the earlier yr.
One resolution 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 will trigger points when utilizing it in customized calculations.
One other resolution 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 crimson, 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.
Curiously, the sums for the quarters and the years are right as properly.
In the intervening time, I don’t see any challenge with utilizing this strategy, and I’ll use and take a look at 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 several states side-by-side:
On the left, you see that each one rows for 2023 are similar when 2022 is collapsed.
On the appropriate, you see the right values for 2023, however they’re displayed solely once I develop no less than 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 concerning 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 info mannequin from scratch, and it labored instantly:
As you may 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 yr.
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 slightly anxious about this as a result of I don’t perceive the rationale 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 every of 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 yr.
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 accurately maps the weeks and weekdays to the earlier yr. Subsequently, this needs to be an issue.
However because the weeks don’t align with the months, I can’t add the Month class. I’ll get an error when validating when making an attempt so as to add the Month class.
Subsequently, I can’t use an MTD calculation, because the operate won’t discover the wanted class:
I can’t 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 right here for Microsoft’s assertion about this.
Since I exploit the YearForWeek column for the Yr class, it won’t 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 fascinating!
To finish on a constructive word, I can present you one thing that works very properly.
Keep in mind the problem with the months that aren’t of the identical size and the way the PY values have been shifted?
This impact doesn’t seem when performing weekly calculations.
As you may see, the outcomes are accurately calculated based mostly on the week and the right weekdays.
As anticipated, the values aren’t mapped to the dates of the earlier yr however to the weekdays per week.
That is what I anticipate when observing outcomes by week and weekdays.
The reason being that every week is similar size, and the date desk is constructed to assist such a situation.
Conclusion
As you may see, the outcomes are combined.
When trying on the outcomes from earlier intervals of various lengths (months or years), the outcomes shift.
When the intervals are of the identical size (weeks or the customized calendar), then all the pieces works as anticipated.
I used to be extraordinarily shocked and upset once I noticed the outcomes for the leap years.
However fortuitously, this may be solved by understanding how the brand new logic works.
The opposite challenge with which I’ve a foul feeling is the inconsistent functioning of the weekly based mostly calendar and the PY calculation.
That is disturbing, because it’s not all the time that straightforward to rebuild an information mannequin.
One other challenge I’ve is that SQLBI reviews 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’ll 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 publish from Chris Webb concerning the results of the calendar-based time intelligence:
Definition of the ISO-Week based mostly on the ISO8601 commonplace
https://www.calendarz.com/weblog/iso-week-numbers-explained-week-1-week-53-and-year-boundaries
Like in my earlier articles, I exploit the Contoso pattern dataset. You possibly can obtain the ContosoRetailDW Dataset without cost from Microsoft right here.
The Contoso Knowledge can be utilized freely beneath the MIT License, as described on this doc. I up to date the dataset to shift the info to up to date dates and eliminated all tables not wanted for this instance.
