THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

DAX Time Intelligence for 4-4-5 Calendar, ISO Calendar and other custom ones #dax #powerpivot

DAX offers a set of Time Intelligence functions that simplify writing DAX expressions such as YTD, YOY and other time-related calculations. However, these functions only works when some assumptions are valid: your periods should be “natural” months and quarter. Some industries, such as retail and manufacturing, are used to accounting periods that are based on weeks instead of months. One month and one quarter are a set of weeks and a week cannot be split in different months, quarter or years. Making DAX working on these custom calendars requires you to write some DAX expression without using the built-in Time Intelligence functions.

I wrote an article, Week-Based Time Intelligence in DAX, which describes how to write the common DAX calculations required on a custom calendar. I also included two samples, one for Excel 2010 and the other for Excel 2013, so that you can easily work on both versions (as you know, downgrading a workbook from Excel 2013 to Excel 2010 with PowerPivot is not possible).

An important tip you will find is that creating a column that contains the number of days elapsed in a year (or the running total of days in the year, if you prefer) makes it easy writing the FILTER required to use the right set of days in each calculation. After all, this technique is very similar to the one you would use in SQL to perform the same calculation, for this reason DAX is considered more intuitive than MDX by developers with a SQL background.

Published Tuesday, February 19, 2013 4:21 PM by Marco Russo (SQLBI)



Di said:

Hi Marco,

Excellent post and it is much better solution, which I used to use DATEADD(dates[date],-91,day) for PQ (fixed 91 days per quarter). However, there is small exception for 4-4-5 calendar. After every 5 or 6 years, there will be a quarter become 5-4-5 and it will cause problem when using your formula.


February 26, 2013 2:41 AM

Marco Russo (SQLBI) said:

As you can read in comments of the article, the best way to calculate a "last 13 weeks" calculation is creating a column containing an IncrementalWeekNumber that never resets at the beginning of each year. It's not clear to me what is the issue in quarter that are exceptions - there is an issue in the formula or with a properly configured Calendar table it should work fine with existing formulas?

February 26, 2013 2:48 AM

Di said:

The exception is due to counting of 364 days/year. So after every 5/6 years, need to add a week to catch up the missed days. For example, we use 26/12/2011 - 02/04/2011 as Q1'11, 03/04/2011 - 02/07/2011 as Q2. I create a column ISO Qtr Day Number (1-98 for Q1, 1-91 for Q2) similar to your sample ISO Year Day Number and create corresponding Iso PQ using

CONTAINS( VALUES( Dates[ISO Qtr Day Number] ), Dates[ISO Qtr Day Number], Dates[ISO Qtr Day Number] ).

It will not count the 92-98 days in Q1 because current context in Q2 only has Qtr day number 1-91.

February 27, 2013 1:23 AM

Marco Russo (SQLBI) said:

Yes, that's correct - however, it's good to know it only affects the way you create the Dates table and doesn't affect the DAX formula used to perform the comparison. The article was intended to show how to approach the problem, it's not a complete pattern for every type of calendar. I hope to be able to find the time to write a more complete example in the future, the problem is that there are many differences for different needs that makes it hard to write a single template that's good for everyone.


February 27, 2013 1:38 AM

Di said:

Agree, the most important is the concept you shared and it is very useful.

Thank you.

March 1, 2013 1:29 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement