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

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

Di — Tue, 26 Feb 2013 07:41:57 GMT

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.

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

Marco Russo (SQLBI) — Tue, 26 Feb 2013 07:48:58 GMT

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?

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

Di — Wed, 27 Feb 2013 06:23:06 GMT

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.

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

Marco Russo (SQLBI) — Wed, 27 Feb 2013 06:38:26 GMT

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.

Thanks!

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

Di — Fri, 01 Mar 2013 06:29:36 GMT

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

Thank you.