Creating a 4-4-5 Fiscal Calendar in Power BI with DAX

I’d like to demonstrate creating a 4-4-5 fiscal calendar with DAX.

Please be forewarned that this article contains some questionable humour... Let’s go!

The Scene

Power BI comes equipped with many time intelligence functions which cater for the standard Gregorian calendar and life is all rather swell.

However, perhaps down to bad luck, or my background as an accountant, I have always been faced with financial periods and weeks based on a 4-4-5 fiscal calendar… bad times.

If, in the event you don’t have a friendly database administrator (I am lucky to have one and they know who they are), you have a few options in creating a 4-4-5 calendar:

1. Convince all your colleagues, senior management and directors to use a Gregorian calendar (best of luck with that, let me know how you get on!)
2. Use your life-long friend Excel to create a calendar and import into Power BI desktop
3. Use Power Query/M language
4. DAX
5. Alchemy

I’m opting for option 4 with a little sprinkling of option 5 for good measure.

The Set-Up

I have opened a blank Power BI desktop file and created a new table by typing the following DAX:

Explanation:

• Line 3: Create a one column table containing contiguous dates between 01/01/2011 and 31/12/2014
• Line 2: For every row, generate additional columns which will be detailed in the second argument of the GENERATE function
• Line 4 – 7: The additional columns are “Date Key” and “Year” which are derived from the one column date table created in line 3

The resulting table contains 1,461 rows and 3 columns.

The Alchemy

OK, so this isn’t really alchemy as I’m not one for proto/pseudoscience. Nonetheless I think it’s pretty cool and hopefully it will prove helpful to others.

From the outset, I am assuming that my fiscal calendar:

• Is 52 weeks duration
• Each week runs Sunday to Saturday
• That week 1 starts on 1st January and ends on the first Saturday of the year unless this would result in week 1 being less than 4 days in duration
• That week 52 begins 350 days (or 50 weeks) after the end of week 1
• Follows a 4-4-5 week pattern

I haven’t seen a fiscal calendar in practice that doesn’t work like this or with very slight variations.

Firstly, I need to determine the week ending date for each week in each year, as the following DAX demonstrates:

Explanation:

• Line 2 – 9: Without getting bogged down in technical details, the key to this is the ALLEXCEPT function. If no restriction was placed on the filter on the “Year” column then the date returned would be the first Saturday occurring between 01/01/2011 – 31/12/2014, being the 01/01/2011
• Line 10 – 15: This is enforcing the 'week 1 must be longer than 4 days' rule
• Line 16: Week 51 will be 50 weeks after the end of week 1
• Line 17 – 24: Again, without getting a serious geek on, this trick has been around since the days of Excel (remember them?... Yeah, me neither). The MOD function will take the second argument, divide the first argument by this and leave the remainder, in maths this is known as the modulo operator (sorry I got my geek on!)

Secondly, the week ending date is useful, but I want to return the week number that the week ending dates represent. I add an additional calculated column to my Calendar table called “Week Number”:

Explanation:

• Line 3 – 6: Return a one column table consisting of the unique week ending dates for each year
• Line 2, 7 -10: Iterate over these dates and rank them in ascending order based on the dates themselves

This simply returns for each year, the week number from 1 through 52.

Finally, from the week number column, I can calculate month number, month name, quarter number and quarter name.

The DAX is straightforward...

Month Number:

Month:

Quarter Number:

Quarter:

The Takeaway

There are many ways to create a 4-4-5 fiscal calendar in Power BI. This is an option if you find yourself without any other means of doing so.

In future articles, I will take things further and add a bit of complexity to the DAX to add some very useful columns to the Calendar table. I hope you will check it out.

Remember folks, live life to the DAX!