Everyone knows the standard Time Intelligence operate based mostly on years, quarters, months, and days. However typically, we have to carry out extra unique timer intelligence calculations. However we should always not overlook to contemplate efficiency whereas programming the measures.
Introduction
There are numerous Dax features in Energy BI for Time Intelligence Measures.
The commonest are:
Yow will discover a complete listing of Time Intelligence features right here: Time Intelligence – DAX Information. These features cowl the commonest instances.
Nonetheless, some necessities can’t be simply lined with these features. And right here we’re.
I wish to cowl a few of these instances I encountered in my initiatives, which embody:
- Final n Durations and a few variants
- How to deal with Leap years
- Week-to-Date calculations
- Calculating Weekly sums
- Fiscal Week YTD
I’ll present you tips on how to use an prolonged date desk to help these eventualities and enhance effectivity and efficiency.
Most Time-Intelligence features work no matter whether or not the Fiscal 12 months is aligned with the calendar yr. One exception is 12 months-to-Date (YTD).
For such instances, have a look at the DATESYTD() operate talked about above. There, you can find the optionally available parameter to go the final day of the Fiscal yr.
The final case will cowl calculations based mostly on weeks, whereas the Fiscal yr doesn’t align with the calendar yr.
Situation
I’ll use the well-known ContosoRetailDW information mannequin.
The Base Measure is Sum On-line Gross sales, which has the next code:
Sum On-line Gross sales = SUMX('On-line Gross sales',
( 'On-line Gross sales'[UnitPrice]
* 'On-line Gross sales'[SalesQuantity] )
- 'On-line Gross sales'[DiscountAmount] )I’ll work virtually solely in DAX-Studio, which gives the Server Timing operate to research the efficiency of the DAX code. Within the References part beneath, you’ll find a hyperlink to an article about tips on how to acquire and interpret efficiency information in DAX Studio.
That is the bottom question utilized in my examples to get some information from the information mannequin:
EVALUATE
CALCULATETABLE(
SUMMARIZECOLUMNS('Date'[Year]
,'Date'[Month Short Name]
,'Date'[Week]
,'Date'[Date]
,"On-line Gross sales", [Sum Online Sales]
)
,'Product'[ProductCategoryName] = "Computer systems" ,'Product'[ProductSubcategoryName] = "Laptops"
,'Buyer'[Continent] = "North America"
,'Buyer'[Country] = "United States" ,'Buyer'[State/Province] = "Texas" )
In most examples, I’ll take away some filters to get extra full information (for every day).
Date desk
My date desk features a comparatively giant variety of extra columns.
Within the references part beneath, you’ll find some articles written by SQLBI, on constructing weekly associated calculations, together with making a date desk to help these calculations.
As described in my article about date tables referenced beneath, I’ve added the next columns:
- Index or Offset columns to depend the times, weeks, months, quarters, semesters, and years from the present date.
- Flag columns to mark the present day, week, month, quarter, semester, and yr based mostly on the present date.
- This and the earlier columns require a each day recalculation to make sure the right date is used because the reference date.
- Begin- and Finish-Dates of every week and month (Add extra if wanted).
- Begin- and Finish-Dates for the Fiscal 12 months.
- Earlier yr dates to incorporate the beginning and finish dates of the present interval. That is particularly fascinating for weeks, because the start- and finish dates of the weeks will not be the identical from yr to yr.
As you will note, I’ll use these columns extensively to simplify my calculations.
As well as, we are going to use the Calendar Hierarchy to calculate the wanted outcomes at totally different ranges of the hierarchy.
A whole Calendar hierarchy accommodates both:
- 12 months
- Semester
- Quarter
- Month
- Day
Or
- 12 months
- Week
- Day
If the Fiscal 12 months doesn’t align with the Calendar yr, I constructed the Hierarchy with the Fiscal 12 months as a substitute of the Calendar 12 months.
Then, I added a separate FiscalMonthName column and a FiscalMonthSort column to make sure that the primary month of the fiscal yr was proven first.
OK, let’s begin with the primary case.
Final n intervals
This situation calculates the rolling sum of values over the previous n intervals.
For instance, for every day, we wish to get the Gross sales for the final 10 days:
Right here is the Measure I got here up with:
On-line Gross sales (Final 10 days) =
CALCULATE (
[Sum Online Sales]
,DATESINPERIOD (
'Date'[Date],
MAX ( 'Date'[Date] ),
-10,
DAY
)
) When executing the question filtering for Computer systems and North America, I get this end result:
If I have a look at the server timings, the end result shouldn’t be dangerous:
As you’ll be able to see, the Storage engine performs greater than half of the work, which is an efficient signal. It’s not excellent, however because the execution time is lower than 100 ms, it’s nonetheless superb from the efficiency perspective.
This method has one essential problem:
When calculating the rolling sum over a number of months, you will need to know that this method is date oriented.
Because of this while you have a look at a particular time, it goes again to the identical day of the given month. For instance:
We have a look at January 12. 2024, and we wish to calculate the rolling sum during the last three months. The beginning date for this calculation can be November 13. 2023.
When will we wish to get the rolling sum for the whole month?
Within the case above, I wish to have because the beginning date November 1, 2023.
For this case, we are able to use the MonthIndex column.
Every column has a novel index based mostly on the present date.
Subsequently, we are able to use it to return three months and get the whole month.
That is the DAX Code for this:
On-line Gross sales rolling full 3 months =
VAR CurDate =
MAX ( 'Date'[Date] )
VAR CurMonthIndex =
MAX ( 'Date'[MonthIndex] )
VAR FirstDatePrevMonth =
CALCULATE (
MIN ( 'Date'[Date] ),
REMOVEFILTERS ( 'Date' ),
'Date'[MonthIndex] = CurMonthIndex - 2
)
RETURN
CALCULATE (
[Sum Online Sales],
DATESBETWEEN (
'Date'[Date],
FirstDatePrevMonth,
CurDate
)
)The execution continues to be fast, nevertheless it’s much less environment friendly, as a lot of the calculations can’t be carried out by the Storage engine:
I attempted different approaches (for instance, 'Date'[MonthIndex] >= CurMonthIndex – 2 && 'Date'[MonthIndex] <= CurMonthIndex), however these approaches have been worse than this one.
Right here is the end result for a similar logic, however for the final two months (To keep away from displaying too many rows):
Concerning Leap Years
The intercalary year downside is odd, which is obvious when calculating the earlier yr for every day. Let me clarify:
Once I execute the next Question to get the final days of February for the years 2020 and 2021:
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
'Date'[Year],
'Date'[Month Short Name],
'Date'[MonthKey],
'Date'[Day Of Month],
"On-line Gross sales", [Sum Online Sales],
"On-line Gross sales (PY)", [Online Sales (PY)]
),
'Date'[Year] IN {2020, 2021},
'Date'[Month] = 2,
'Date'[Day Of Month] IN {27, 28, 29},
'Buyer'[Continent] = "North America",
'Buyer'[Country] = "United States"
)
ORDER BY 'Date'[MonthKey],
'Date'[Day Of Month]I get the next end result:
As you’ll be able to see above, the end result for February 28. 2020 is proven twice, and at some point is lacking the February 2021 for On-line Gross sales (PY).
When wanting on the month, the sum is appropriate:
The issue is that there isn’t a February 29 in 2021. Subsequently, there isn’t a means that the gross sales for February 29, 2020 can be displayed when itemizing the Gross sales Quantity per day.
Whereas the result’s appropriate, will probably be incorrect when the information is exported to Excel, and the values are summed. Then, the sum of the each day outcomes will differ from these proven for the whole month.
This may undermine the customers’ perceived reliability of the information.
My answer was so as to add a LeapYearDate desk. This desk is a duplicate of the Date desk however with no Date column. I added one row every year on February 29, even for non-leap years.
Then, I added a calculated column for every month and day (MonthDay):
MonthDay = ('LeapYearDate'[Month] * 100 ) + 'LeapYearDate'[Day Of Month]The Measure to calculate the earlier yr manually and utilizing the brand new desk is the next:
On-line Gross sales (PY Leap 12 months) =
VAR ActYear =
SELECTEDVALUE ( 'LeapYearDate'[Year] )
VAR ActDays =
VALUES ( 'LeapYearDate'[MonthDay] )
RETURN
CALCULATE (
[Sum Online Sales],
REMOVEFILTERS ( LeapYearDate ),
'LeapYearDate'[Year] = ActYear - 1,
ActDays
)As you’ll be able to see, I acquired the present yr, and by utilizing the VALUES() operate, I acquired the listing of all dates within the present filter context.
Utilizing this methodology, my Measure works for single Days, Months, Quarters, and Years. The results of this Measure is the next:
As you’ll be able to see right here, the Measure may be very environment friendly, as a lot of the work is finished by the Storage engine:
However, to be trustworthy, I don’t like this method, although it really works very nicely.
The reason being that the LeapYearDate desk doesn’t have a date column. Subsequently, it can’t be used as a Date desk for the present Time Intelligence features.
We should additionally use the calendar columns from this desk within the visualizations. We can’t use the extraordinary date desk.
Consequently, we should reinvent all Time Intelligence features to make use of this desk.
I strongly advocate utilizing this method solely when vital.
Week to Date and PY
Some Enterprise areas focus on Weekly evaluation.
Sadly, the usual Time Intelligence features don’t help weekly evaluation out of the field. Subsequently, we should construct our Weekly Measures by ourselves.
The primary Measure is WTD.
The primary method is the next:
On-line Gross sales WTD v1 =
VAR MaxDate = MAX('Date'[Date])
VAR CurWeekday = WEEKDAY(MaxDate, 2)
RETURN
CALCULATE([Sum Online Sales]
,DATESBETWEEN('Date'[Date]
,MaxDate - CurWeekDay + 1 ,MaxDate)
)As you’ll be able to see, I exploit the WEEKDAY() operate to calculate the beginning date of the week. Then, I exploit the DATESBETWEEN() operate to calculate the WTD.
While you adapt this sample to your state of affairs, you will need to be sure that the second parameter in WEEKDAY() is about to the right worth. Please learn the documentation to be taught extra about it.
The result’s the next:
One other method is to retailer the primary date of every week within the Date desk and use this data within the Measure:
On-line Gross sales WTD PY v2 =
VAR DayOfWeek = MAX('Date'[Day Of Week])
VAR FirstDayOfWeek = MIN('Date'[FirstDayOfWeekDatePY])
RETURN
CALCULATE([Sum Online Sales]
,DATESBETWEEN('Date'[Date]
,FirstDayOfWeek
,FirstDayOfWeek + DayOfWeek - 1)
)
The result’s exactly the identical.
When analyzing the efficiency in DAX Studio, I see that each Measures are comparable to one another:
I have a tendency to make use of the second, because it has higher potential when mixed with different Measures. However in the long run, it will depend on the present situation.
One other problem is to calculate the earlier yr.
Take a look at the next dates for a similar week in numerous weeks:
As you’ll be able to see, the dates are shifted. And as the usual time intelligence features are based mostly on shifting dates, they won’t work.
I attempted totally different approaches, however in the long run, I saved the primary date of the identical week for the earlier yr within the date desk and used it like within the second model of WTD proven above:
On-line Gross sales WTD PY =
VAR DayOfWeek = MAX('Date'[Day Of Week])
VAR FirstDayOfWeek = MIN('Date'[FirstDayOfWeekDatePY])
RETURN
CALCULATE([Sum Online Sales]
,DATESBETWEEN('Date'[Date]
,FirstDayOfWeek
,FirstDayOfWeek + DayOfWeek - 1)
)
That is the end result:
Because the logic is similar as within the WTD v2, the efficiency can also be the identical. Subsequently, this Measure may be very environment friendly.
Weekly Sums for PY
Generally, the weekly view is sufficient, and we don’t must calculate the WTD on the Each day degree.
We don’t want a WTD Measure for this situation for the present yr. The bottom Measure sliced by Week can cowl this. The result’s appropriate out of the field.
However, once more, it’s one other story for PY.
That is the primary model I got here up with:
On-line Gross sales (PY Weekly) v1] =
VAR ActYear = MAX('Date'[Year])
RETURN
CALCULATE([Sum Online Sales]
,ALLEXCEPT('Date'
,'Date'[Week]
)
,'Date'[Year] = ActYear - 1
)
Right here, I subtract one from the present yr whereas retaining the filter for the present week. That is the end result:
The efficiency is sweet, however I can do higher.
What if I might retailer a novel Week Identifier within the Date column?
For instance, the Present Week is 9 of 2025..
The Identifier could be 202509.
Once I detract 100 from it, I get 202409, the identifier for a similar week within the earlier yr. After including this column to the date desk, I can change the Measure to this:
MEASURE 'All Measures'[Online Sales (PY Weekly) v2] =
VAR WeeksPY = VALUES('Date'[WeekKeyPY])
RETURN
CALCULATE([Sum Online Sales]
,REMOVEFILTERS('Date')
,'Date'[WeekKey] IN WeeksPY
)
This model is way easier than earlier than, and the end result continues to be the identical.
After we evaluate the execution statistics of the 2 variations, we see this:
As you’ll be able to see, the second model, with the precalculated column within the Date desk, is barely extra environment friendly. I’ve solely 4 SE queries, a great signal for elevated effectivity.
Fiscal Weeks YTD
This final one is difficult.
The requirement is that the consumer needs to see a YTD ranging from the primary day of the primary week of the Fiscal yr.
For instance, the Fiscal yr begins on July 1.
In 2022, the week containing July the 1st begins on Monday, June 27.
Because of this the YTD calculation should begin on this date.
The identical applies to the YTD PY calculation beginning Monday, June 28, 2021.
This method has some penalties when visualizing the information.
Once more, figuring out if the end result should be proven on the day or week degree is crucial. When displaying the information on the day degree, the end result might be complicated when choosing a Fiscal 12 months:
As you’ll be able to see, Friday is the primary day of the Fiscal yr. And the YTD end result doesn’t begin on July 1st however on Monday of that week.
The consequence is that the YTD doesn’t appear to begin accurately. The customers should know what they’re taking a look at.
The identical is legitimate for the YTD PY outcomes.
To facilitate the calculations, I added extra columns to the Date desk:
- FiscalYearWeekYear—This subject accommodates the numerical illustration of the Fiscal yr (for 23/24, I get 2324), beginning with the primary week of the Fiscal yr.
- FiscalYearWeekYearPY – The identical as earlier than, however for the earlier yr (FiscalYearWeekYear – 101).
- FiscalWeekSort—This sorting column begins the week with the primary day of the fiscal yr. A extra elaborate means to make use of this column might be to comply with the ISO-Week definition, which I didn’t do to maintain it easier.
- FiscalYearWeekSort – The identical as earlier than however with the FiscalYearWeekYear in entrance (e. g. 232402).
- FirstDayOfWeekDate – The date of the Monday of the week wherein the present date is in.
Right here is the Measure for the Each day YTD:
On-line Gross sales (Fiscal Week YTD) =
VAR FiscalYearWeekYear = MAX('Date'[FiscalYearWeekYear])
VAR StartFiscalYear = CALCULATE(MIN('Date'[Date])
,REMOVEFILTERS('Date')
,'Date'[FiscalYearWeekSort] =
FiscalYearWeekYear * 100 + 1
)
VAR FiscalYearStartWeekDate = CALCULATE(MIN('Date'[FirstDayOfWeekDate])
,ALLEXCEPT('Date'
,'Date'[FiscalYearWeekYear]
)
,'Date'[Date] = StartFiscalYear
)
VAR MaxDate = MAX('Date'[Date])
RETURN
CALCULATE([Sum Online Sales]
,REMOVEFILTERS('Date')
,DATESBETWEEN('Date'[Date]
,FiscalYearStartWeekDate
,MaxDate
)Right here is the DAX Code for the Each day YTD PY:
On-line Gross sales (Fiscal Week YTD) (PY)] =
VAR FiscalYearWeekYear = MAX('Date'[FiscalYearWeekYear])
-- Get the Week/Weekday in the beginning of the present Fiscal 12 months
VAR FiscalYearStart = CALCULATE(MIN('Date'[Date])
,REMOVEFILTERS('Date')
,'Date'[FiscalYearWeekSort] =
FiscalYearWeekYear * 100 + 1
)
VAR MaxDate = MAX('Date'[Date])
-- Get the variety of Days because the begin of the FiscalYear
VAR DaysFromFiscalYearStart =
DATEDIFF( FiscalYearStart, MaxDate, DAY )
-- Get the PY Date of the Fiscal 12 months Week Begin date
VAR DateWeekStartPY = CALCULATE(MIN('Date'[Date])
,REMOVEFILTERS('Date')
,'Date'[FiscalYearWeekSort] =
(FiscalYearWeekYear - 101) * 100 + 1
)
RETURN
CALCULATE(
[Sum Online Sales],
DATESBETWEEN(
'Date'[Date],
DateWeekStartPY,
DateWeekStartPY + DaysFromFiscalYearStart
)
)As you’ll be able to see, each Measures comply with the identical sample:
- Get the present Fiscal 12 months.
- Get the Beginning Date of the present Fiscal 12 months.
- Get the Beginning date of the week beginning the Fiscal 12 months.
- Calculate the Consequence based mostly on the Distinction between these two dates
For the PY Measure, one extra step is required:
- Calculate the times between the beginning and present dates to calculate the right YTD. That is vital due to the date shift between the years.
And right here is the DAX code for the weekly base YTD:
On-line Gross sales (Fiscal Week YTD) =
VAR FiscalWeekSort = MAX( 'Date'[FiscalWeekSort] )
-- Get the Week/Weekday in the beginning of the present Fiscal 12 months
VAR FiscalYearNumber = MAX( 'Date'[FiscalYearWeekYear] )
RETURN
CALCULATE(
[Sum Online Sales],
REMOVEFILTERS('Date'),
'Date'[FiscalYearWeekSort] >= (FiscalYearNumber * 100 ) + 1
&& 'Date'[FiscalYearWeekSort] <= (FiscalYearNumber * 100 ) +
FiscalWeekSort
)For the weekly YTD PY, the DAX code is the next:
On-line Gross sales (Fiscal Week YTD) (PY) =
VAR FiscalWeekSort = MAX( 'Date'[FiscalWeekSort] )
-- Get the Week/Weekday in the beginning of the present Fiscal 12 months
VAR FiscalYearNumberPY = MAX( 'Date'[FiscalYearWeekYearPY] )
RETURN
CALCULATE(
[Sum Online Sales],
REMOVEFILTERS('Date'),
'Date'[FiscalYearWeekSort] >= (FiscalYearNumberPY * 100) + 1
&& 'Date'[FiscalYearWeekSort] <= (FiscalYearNumberPY * 100) +
FiscalWeekSort
)Once more, each Measures comply with the identical sample:
- Get the present (Kind-) variety of the week within the Fiscal yr.
- Get the beginning date for the fiscal yr’s first week.
- Calculate the end result based mostly on these values.
The end result for the weekly based mostly Measure is the next (On the weekly degree, as the worth is the similar for every day of the identical week):
When evaluating the 2 Approaches, the Measure for the weekly calculation is extra environment friendly than the one for the each day calculation:
As you’ll be able to see, the Measure for the weekly result’s quicker, has a extra good portion executed within the Storage Engine (SE), and has fewer SE queries.
Subsequently, it may be a good suggestion to ask the customers in the event that they want a WTD end result on the day degree or if it’s sufficient to see the outcomes on the week degree.
Conclusion
While you begin writing Time Intelligence expressions, think about whether or not extra calculated columns in your date desk might be useful.
A rigorously crafted and prolonged date desk might be useful for 2 causes:
- Make Measures simpler to write down
- Enhance the efficiency of the Measures
They are going to be simpler to write down as I don’t must carry out the calculations to get the middleman outcomes to calculate the required outcomes.
The consequence of shorter and easier Measures is healthier effectivity and efficiency.
I’ll add increasingly more columns to the template of my date desk as I encounter extra conditions wherein they are often useful.
One query stays: Learn how to construct it?
In my case, I used an Azure SQL database to create the desk utilized in my examples.
Nevertheless it’s attainable to create a date desk as a DAX desk or use Python or JavaScript in Cloth or no matter information platform you employ.
An alternative choice is to make use of the Bravo device from SQLBI, which lets you create a DAX desk containing extra columns to help unique Time Intelligence eventualities.
References
Yow will discover extra details about my date-table right here.
Learn this piece to discover ways to extract efficiency information in DAX-Studio and tips on how to interpret it.
An SQLBI article about constructing a date desk to help weekly calculations: Utilizing weekly calendars in Energy Bi – SQLBI
SQLBI Sample to carry out additional weekly calculations:
Week-related calculations – DAX Patterns
Like in my earlier articles, I exploit the Contoso pattern dataset. You possibly can obtain the ContosoRetailDW Dataset free of charge from Microsoft right here.
The Contoso Information might be freely used beneath the MIT License, as described right here.
I modified the dataset to shift the information to modern dates.
