In my last blog on PowerPivotPro.com I showed how easy it is to create a standard calendar for Power Pivot using Power Query. Most of my customers however don’t use a standard calendar but instead use a 445 calendar (which is very common in the Retail Industry). A lot of people shared with me their 445 calendars after my last post, and that reminded me that although the concept of a 445 calendar is very common, everyone seems to have different rules on how the calendar works.
Differences in 445 Calendars include
- What month you start the financial year
- What day of the week is the start of the week.
- 365 divided by 7 = 52 + 1 day remainder. Different companies handle the extra day in different ways.
So there are almost as many permutations as companies and I don’t want to go down the path of trying to write 1 calendar that will work for everyone. Instead this post covers the techniques I used to solve the 445 calendar problem for one of my customers. If you are so inclined, you can copy these techniques plus some of your own to meet your own 445 needs. To give you an idea, it took me about an hour to think through the problems, research the functions and build a working calendar. If you copy my techniques, you should get a head start on that for your own 445.
The rules of this particular calendar are
- The first day of the financial year is the Monday on or before 1 May.
- The number of weeks each month are in the pattern 454 (4 for May, 5 for June, 4 for July) and then repeats.
- The calendar weeks, months and years mirror the financial data. So Mon 31 Dec 2013 is the first week of financial week 36. Even though the date is actually in the year 2013, it is treated as part of the first calendar week of 2014.
Here is how I did it. But first a word of warning. The steps are easiest to follow if you first download the sample workbook (link at the bottom) and step through the Applied Steps as you read my explanations in this post. If you are not a Power Query expert and you just read the post, it will quite hard to understand. So do yourself a favour and download the workbook, and step through it as you read the post