Advanced Time Intelligence in DAX with Performance in Mind

Everyone knows the standard Time Intelligence function based on years, quarters, months, and days. But sometimes, we’d like to perform more exotic timer intelligence calculations. But we should always not forget to think about performance while programming the measures. 

Introduction 

There are numerous Dax functions in Power BI for Time Intelligence Measures. 

Probably the most common are: 

You’ll find a comprehensive list of Time Intelligence functions here: Time Intelligence – DAX Guide. These functions cover probably the most common cases. 

Nonetheless, some requirements can’t be easily covered with these functions. And here we’re. 

I need to cover a few of these cases I encountered in my projects, which include: 

• Last n Periods and a few variants 
• The way to address Leap years 
• Week-to-Date calculations 
• Calculating Weekly sums 
• Fiscal Week YTD 

I’ll show you use an prolonged date table to support these scenarios and improve efficiency and performance. 

Most Time-Intelligence functions work no matter whether the Fiscal Yr is aligned with the calendar yr. One exception is Yr-to-Date (YTD). 

For such cases, have a look at the DATESYTD() function mentioned above. There, one can find the optional parameter to pass the last day of the Fiscal yr. 

The last case will cover calculations based on weeks, while the Fiscal yr doesn’t align with the calendar yr. 

Scenario 

I’ll use the well-known ContosoRetailDW data model.

The Base Measure is Sum Online Sales, which has the next code: 

Sum Online Sales = SUMX('Online Sales',
 ( 'Online Sales'[UnitPrice]
            * 'Online Sales'[SalesQuantity] ) 
                         - 'Online Sales'[DiscountAmount] )

I’ll work almost exclusively in DAX-Studio, which provides the Server Timing function to investigate the performance of the DAX code. Within the References section below, you will discover a link to an article about collect and interpret performance data in DAX Studio. 

That is the bottom query utilized in my examples to get some data from the info model: 

EVALUATE 
 CALCULATETABLE( 
 SUMMARIZECOLUMNS('Date'[Year] 
 ,'Date'[Month Short Name] 
,'Date'[Week] 
,'Date'[Date] 
,"Online Sales", [Sum Online Sales] 
) 
 ,'Product'[ProductCategoryName] = "Computers" ,'Product'[ProductSubcategoryName] = "Laptops" 
,'Customer'[Continent] = "North America" 
 ,'Customer'[Country] = "United States"  ,'Customer'[State/Province] = "Texas" )

In most examples, I’ll remove some filters to get more complete data (for every day). 

Date table 

My date table includes a comparatively large variety of additional columns. 

Within the references section below, you will discover some articles written by SQLBI, on constructing weekly related calculations, including making a date table to support these calculations. 

As described in my article about date tables referenced below, I even have added the next columns: 

• Index or Offset columns to count 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 on the present date. 
• This and the previous columns require a day by day recalculation to make sure the proper date is used because the reference date. 
• Start- and End-Dates of every week and month (Add more if needed). 
• Start- and End-Dates for the Fiscal Yr. 
• Previous yr dates to incorporate the beginning and end dates of the present period. This is particularly interesting for weeks, because the start- and end dates of the weeks should not the identical from yr to yr. 

As you will notice, I’ll use these columns extensively to simplify my calculations.

As well as, we are going to use the Calendar Hierarchy to calculate the needed results at different levels of the hierarchy. 

A whole Calendar hierarchy comprises either: 

1. Yr 
2. Semester 
3. Quarter 
4. Month 
5. Day 

Or 

1. Yr 
2. Week 
3. Day 

If the Fiscal Yr doesn’t align with the Calendar yr, I built the Hierarchy with the Fiscal Yr as a substitute of the Calendar Yr. 

Then, I added a separate FiscalMonthName column and a FiscalMonthSort column to be certain that the primary month of the fiscal yr was shown first. 

OK, let’s start with the primary case. 

Last n periods 

This scenario calculates the rolling sum of values over the past n periods. 

For instance, for every day, we wish to get the Sales for the last 10 days: 

Here is the Measure I got here up with: 

Online Sales (Last 10 days) = 
 CALCULATE (
 [Sum Online Sales] 
 ,DATESINPERIOD ( 
 'Date'[Date], 
MAX ( 'Date'[Date] ), 
-10, 
DAY 
 ) 
 ) 

When executing the query filtering for Computers and North America, I get this result:

If I have a look at the server timings, the result is just not bad: 

As you’ll be able to see, the Storage engine performs greater than half of the work, which is a great sign. It’s not perfect, but because the execution time is lower than 100 ms, it’s still excellent from the performance viewpoint. 

This approach has one crucial issue:

When calculating the rolling sum over multiple months, you could know that this approach is date oriented. 

Because of this whenever you have a look at a selected time, it goes back 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 starting date for this calculation might be November 13. 2023. 

When do we wish to get the rolling sum for all the month? 

Within the case above, I need to have because the starting date November 1, 2023. 

For this case, we are able to use the MonthIndex column. 

Each column has a novel index based on the present date. 

Due to this fact, we are able to use it to return three months and get all the month. 

That is the DAX Code for this: 

Online 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 quick, however it’s less efficient, as many of the calculations can’t be performed by the Storage engine:

I attempted other approaches (for instance, 'Date'[MonthIndex] >= CurMonthIndex – 2 && 'Date'[MonthIndex] <= CurMonthIndex), but these approaches were worse than this one. 

Here is the result for a similar logic, but for the last two months (To avoid showing too many rows):

Regarding Leap Years 

The bissextile year problem is odd, which is obvious when calculating the previous yr for every day. Let me explain: 

After I execute the next Query to get the last days of February for the years 2020 and 2021: 

EVALUATE 
CALCULATETABLE ( 
 SUMMARIZECOLUMNS ( 
 'Date'[Year], 
 'Date'[Month Short Name], 
 'Date'[MonthKey],
 'Date'[Day Of Month], 
 "Online Sales", [Sum Online Sales], 
 "Online Sales (PY)", [Online Sales (PY)] 
 ), 
 'Date'[Year] IN {2020, 2021}, 
 'Date'[Month] = 2, 
 'Date'[Day Of Month] IN {27, 28, 29}, 
 'Customer'[Continent] = "North America", 
 'Customer'[Country] = "United States" 
) 
 ORDER BY 'Date'[MonthKey], 
 'Date'[Day Of Month]

I get the next result: 

As you'll be able to see above, the result for February 28. 2020 is shown twice, and sooner or later is missing the February 2021 for Online Sales (PY). 

When the month, the sum is correct: 

The issue is that there isn't a February 29 in 2021. Due to this fact, there isn't a way that the sales for February 29, 2020 might be displayed when listing the Sales Amount per day. 

While the result's correct, it should be flawed when the info is exported to Excel, and the values are summed. Then, the sum of the day by day results will differ from those shown for all the month. 

This may undermine the users’ perceived reliability of the info. 

My solution was so as to add a LeapYearDate table. This table is a duplicate of the Date table but and not using a 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 previous yr manually and using the brand new table is the next:

Online Sales (PY Leap Yr) = 
 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 got the present yr, and through the use of the VALUES() function, I got the list of all dates in the present filter context. 

Using this method, 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 here, the Measure may be very efficient, as many of the work is completed by the Storage engine:

But, to be honest, I don’t like this approach, regardless that it really works thoroughly. 

The rationale is that the LeapYearDate table doesn't have a date column. Due to this fact, it can't be used as a Date table for the present Time Intelligence functions. 

We must also use the calendar columns from this table within the visualizations. We cannot use the atypical date table. 

Consequently, we must reinvent all Time Intelligence functions to make use of this table.

I strongly recommend using this approach only when needed. 

Week to Date and PY 

Some Business areas think about Weekly evaluation. 

Unfortunately, the usual Time Intelligence functions don't support weekly evaluation out of the box. Due to this fact, we must construct our Weekly Measures by ourselves. 

The primary Measure is WTD. 

The primary approach is the next: 

Online 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() function to calculate the beginning date of the week. Then, I exploit the DATESBETWEEN() function to calculate the WTD. 

While you adapt this pattern to your situation, you could be certain that the second parameter in WEEKDAY() is ready to the proper value. Please read the documentation to learn more about it. 

The result's the next:

One other approach is to store the primary date of every week within the Date table and use this information within the Measure: 

Online 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 precisely the identical. 

When analyzing the performance in DAX Studio, I see that each Measures are comparable to one another:

I are likely to use the second, because it has higher potential when combined with other Measures. But ultimately, it depends upon the present scenario. 

One other challenge is to calculate the previous yr. 

Take a look at the next dates for a similar week in several weeks: 

As you'll be able to see, the dates are shifted. And because the standard time intelligence functions are based on shifting dates, they are going to not work. 

I attempted different approaches, but ultimately, I stored the primary date of the identical week for the previous yr within the date table and used it like within the second version of WTD shown above: 

Online 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 result: 

Because the logic is similar as within the WTD v2, the performance can be the identical. Due to this fact, this Measure may be very efficient. 

Weekly Sums for PY 

Sometimes, the weekly view is enough, and we don’t must calculate the WTD on the Day by day level. 

We don’t need a WTD Measure for this scenario for the present yr. The bottom Measure sliced by Week can cover this. The result's correct out of the box. 

But, again, it’s one other story for PY.

That is the primary version I got here up with: 

Online Sales (PY Weekly) v1] = 
 VAR ActYear = MAX('Date'[Year]) 
  
 RETURN 
 CALCULATE([Sum Online Sales] 
 ,ALLEXCEPT('Date' 
 ,'Date'[Week] 
) 
 ,'Date'[Year] = ActYear - 1 
 )

Here, I subtract one from the present yr while retaining the filter for the present week. That is the result:

The performance is sweet, but I can do higher. 

What if I could store a novel Week Identifier within the Date column? 

For instance, the Current Week is 9 of 2025.. 

The Identifier could be 202509. 

After I detract 100 from it, I get 202409, the identifier for a similar week within the previous yr. After adding this column to the date table, 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 version is way simpler than before, and the result continues to be the identical. 

After we compare the execution statistics of the 2 versions, we see this: 

As you'll be able to see, the second version, with the precalculated column within the Date table, is barely more efficient. I even have only 4 SE queries, a great sign for increased efficiency. 

Fiscal Weeks YTD 

This last one is difficult. 

The requirement is that the user desires to see a YTD ranging from the primary day of the primary week of the Fiscal yr. 

For instance, the Fiscal yr starts on July 1. 

In 2022, the week containing July the 1^st starts on Monday, June 27. 

Because of this the YTD calculation must start on this date. 

The identical applies to the YTD PY calculation starting Monday, June 28, 2021. 

This approach has some consequences when visualizing the info. 

Again, knowing if the result should be shown on the day or week level is crucial. When showing the info on the day level, the result will be confusing when choosing a Fiscal Yr:

As you'll be able to see, Friday is the primary day of the Fiscal yr. And the YTD result doesn’t start on July 1^st but on Monday of that week. 

The consequence is that the YTD doesn’t seem to begin appropriately. The users must know what they're . 

The identical is valid for the YTD PY results. 

To facilitate the calculations, I added more columns to the Date table: 

• FiscalYearWeekYear—This field comprises the numerical representation of the Fiscal yr (for 23/24, I get 2324), starting with the primary week of the Fiscal yr. 
• FiscalYearWeekYearPY – The identical as before, but for the previous yr (FiscalYearWeekYear – 101). 
• FiscalWeekSort—This sorting column starts the week with the primary day of the fiscal yr. A more elaborate option to use this column could possibly be to follow the ISO-Week definition, which I didn’t do to maintain it more straightforward. 
• FiscalYearWeekSort – The identical as before but with the FiscalYearWeekYear in front (e. g. 232402). 
• FirstDayOfWeekDate – The date of the Monday of the week through which the present date is in.

Here is the Measure for the Day by day YTD:

Online 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
)

Here is the DAX Code for the Day by day YTD PY:

Online Sales (Fiscal Week YTD) (PY)] =
VAR FiscalYearWeekYear = MAX('Date'[FiscalYearWeekYear])
-- Get the Week/Weekday firstly of the present Fiscal Yr
VAR FiscalYearStart = CALCULATE(MIN('Date'[Date])
,REMOVEFILTERS('Date')
,'Date'[FiscalYearWeekSort] =

FiscalYearWeekYear * 100 + 1
)
VAR MaxDate = MAX('Date'[Date])
-- Get the variety of Days for the reason that start of the FiscalYear
VAR DaysFromFiscalYearStart =
DATEDIFF( FiscalYearStart, MaxDate, DAY )
-- Get the PY Date of the Fiscal Yr Week Start 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 follow the identical pattern: 

1. Get the present Fiscal Yr. 
2. Get the Starting Date of the present Fiscal Yr. 
3. Get the Starting date of the week starting the Fiscal Yr. 
4. Calculate the Result based on the Difference between these two dates 

For the PY Measure, one additional step is required: 

• Calculate the times between the starting and current dates to calculate the proper YTD. That is needed due to the date shift between the years. 

And here is the DAX code for the weekly base YTD: 

Online Sales (Fiscal Week YTD) =
VAR FiscalWeekSort = MAX( 'Date'[FiscalWeekSort] )
-- Get the Week/Weekday firstly of the present Fiscal Yr
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: 

Online Sales (Fiscal Week YTD) (PY) =
VAR FiscalWeekSort = MAX( 'Date'[FiscalWeekSort] )
-- Get the Week/Weekday firstly of the present Fiscal Yr
VAR FiscalYearNumberPY = MAX( 'Date'[FiscalYearWeekYearPY] )
RETURN
CALCULATE(
[Sum Online Sales],
REMOVEFILTERS('Date'),
'Date'[FiscalYearWeekSort] >= (FiscalYearNumberPY * 100) + 1
&& 'Date'[FiscalYearWeekSort] <= (FiscalYearNumberPY * 100) +
FiscalWeekSort
)

Again, each Measures follow the identical pattern: 

1. Get the present (Sort-) variety of the week within the Fiscal yr.
2. Get the beginning date for the fiscal yr’s first week.
3. Calculate the result based on these values.

The result for the weekly based Measure is the next (On the weekly level, as the worth is the same for every day of the identical week): 

When comparing the 2 Approaches, the Measure for the weekly calculation is more efficient than the one for the day by day calculation:

As you'll be able to see, the Measure for the weekly result is quicker, has a more significant slice executed within the Storage Engine (SE), and has fewer SE queries. 

Due to this fact, it may well be a great idea to ask the users in the event that they need a WTD result on the day level or if it’s enough to see the outcomes on the week level. 

Conclusion 

While you start writing Time Intelligence expressions, consider whether additional calculated columns in your date table will be helpful. 

A rigorously crafted and prolonged date table will be helpful for 2 reasons: 

• Make Measures easier to jot down 
• Improve the performance of the Measures 

They might be easier to jot down as I don't must perform the calculations to get the intermediary results to calculate the required results. 

The consequence of shorter and simpler Measures is best efficiency and performance. 

I'll add an increasing number of columns to the template of my date table as I encounter more situations through which they will be helpful. 

One query stays: The way to construct it? 

In my case, I used an Azure SQL database to create the table utilized in my examples. 

However it’s possible to create a date table as a DAX table or use Python or JavaScript in Fabric or whatever data platform you utilize. 

Another choice is to make use of the Bravo tool from SQLBI, which means that you can create a DAX table containing additional columns to support exotic Time Intelligence scenarios. 

References 

You'll find more details about my date-table here. 

Read this piece to learn extract performance data in DAX-Studio and interpret it. 

An SQLBI article about constructing a date table to support weekly calculations: Using weekly calendars in Power Bi – SQLBI 

SQLBI Pattern to perform further weekly calculations: 

Week-related calculations – DAX Patterns 

Like in my previous articles, I exploit the Contoso sample dataset. You may download the ContosoRetailDW Dataset totally free from Microsoft here. 

The Contoso Data will be freely used under the MIT License, as described here.

I modified the dataset to shift the info to contemporary dates.