Introduction
After the preliminary euphoria with the brand new calendar-based time intelligence, I began wanting deeper into the brand new function to see what these new potentialities imply in the true 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 understanding.
However over time, I noticed there are darker sides to this shiny new function.
Now I’ll present you 4 examples, the place I found attention-grabbing results.
I’ll provide workarounds or options to every difficulty when doable.
Setup of the Calendars
For this piece, I used two Energy BI studies 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 contains the YearOfWeek column for the yr class.
This column incorporates the week-aligned yr, which is required for such a calendar. This column is predicated on the ISO-week definition. Every year begins on Monday of week 1.
You could find a proof 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 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 consequence may be very attention-grabbing, take a look at this one:
In each instances, the consequence may 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 Complete 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 positively a unique conduct of the operate, which you need to concentrate on. This is applicable in every single place 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, every part seems to be nice.
Now take 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 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 is named “Distance from Guardian”.
However the Guardian is outlined by the third parameter of DATEADD(): 12 months
Subsequently, DATEADD() calculates the gap from the start of the yr and returns the consequence utilizing the identical distance for the earlier yr.
One resolution to this difficulty is to make sure that all months are of equal size.
In my first article about this new function, linked within the References part under, 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 method 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 pink, you see the identical outcomes as earlier than, shifted by in the future.
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.
For the time being, I don’t see any difficulty with utilizing this method, 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 every one rows for 2023 are an identical when 2022 is collapsed.
On the suitable, you see the right values for 2023, however they’re displayed solely once I develop not 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 function (Hyperlink under).
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 rigorously, 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 through the use of the identical columns.
However I’m slightly 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 among my purchasers needs to see a report exhibiting the each 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 appropriately 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 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 right 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 resolve all the necessities.
Weekly calculations – That’s attention-grabbing!
To finish on a constructive notice, I can present you one thing that works very effectively.
Keep in mind the difficulty 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 may see, the outcomes are appropriately 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 help such a state of affairs.
Conclusion
As you may see, the outcomes are combined.
When wanting 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 every part works as anticipated.
I used to be extraordinarily stunned and upset once I noticed the outcomes for the leap years.
However happily, this may be solved by understanding how the brand new logic works.
The opposite difficulty 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 straightforward to rebuild an information mannequin.
One other difficulty I’ve is that SQLBI studies potential points when utilizing a number of calendars in the identical date desk of their article. I’ve added a hyperlink to it under.
This can 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 resolve.
However we are going to see how this function will evolve, as we’re nonetheless in Preview.
References
The SQLBI article explaining the Calendar-based time intelligence function 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 function (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 put up from Chris Webb concerning the results of the calendar-based time intelligence:
Definition of the ISO-Week based mostly on the ISO8601 normal
https://www.calendarz.com/weblog/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 may obtain the ContosoRetailDW Dataset at no cost from Microsoft right here.
The Contoso Information 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.
