In my last blog on p3adaptive.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
First I added a list of dates making sure that the date I added was the first day of the financial year. I then added an ID column. I used the same techniques here as I discussed in my last post (including changing the end date so it is dynamic), so I wont cover it again here – the link is at the top of the page.
Then I inserted a Year ID column (1 for the first year, 2 for the second year etc). To make this work, I simply divided the ID column by 364 and rounded up. So each 364 days gets its own year number – this is how this customer’s calendar works – every year has 364 days.
This RoundUp trick is actually the key to this entire calendar – I have used it many times – so it is worth calling it out here. All I am doing above is dividing the unique ID number I created (1, 2, 3, 4……363, 364, 365, 366 etc) by 364 and rounding up. So
1/364 = a very small number which when rounded up = 1. 2/364 rounded up also equals 1, as does 3/364 etc all the way up to and including 364/364 also rounds up to 1. But 365/364 rounds up to 2 – hence it becomes the “second year”. You will see this pattern over and over below.
Now that I had a reference for which year we were in (first year, second year etc), I could create the Fin Year column. I simply used the starting date as I seeded above, converted it to a year (2013 in this case) and then added the YearID. In this calendar I am writing, the financial year value is taken from year end date – which is 2014 in this case. So the year of the starting date plus the value of the YearID = the Financial Year.
The next step required me to understand the 454 pattern. The 454 pattern follows a 13 week cycle, so I needed to be able to give each week a unique ID so I could use these ID numbers to map the weeks into the correct months. I used the same RoundUp trick I used above here. Just divide the original ID number by 7 and round up. This gives me unique number for each week across the entire calendar
I then created a “week of quarter” column. This one repeats from 1 to 13 and then starts again. I can use this to identify exactly which weeks go into the 454 pattern. This formula is a little different in that counts 1 through 13 and then restarts at 1 again. To do this, it keeps track of how many batches of 13 weeks have already passed using the RoundUp trick and then subtracting 1. So in the first 13 week period it subtracts 0 x 13 from the WeekID (leaving the numbers 1 through 13 for the week numbers. In the second batch of 13 weeks it subtracts 1 x 13 = 13 from the WeekID numbers, so instead of 14 through 26 you end up with 14 subtract 13 through 26 subtract 13, which is 1 through 13 again. The pattern just repeats.
Creating the MonthID was the trickiest part. I had to use 3 steps to do this. Power Query doesn’t have a “Select Case” concept. I Googled it and found one of [link removed due to 404] Chris Webb’s blog posts on the topic. He wrote his own version of Select Case, but it looked too hard to me. So this is the way I did it. I referred to the Week of Quarter Column above. This column has numbers from 1 through 13, then starts again for the second quarter and so on. So using this column, I know that weeks 1, 2, 3 and 4 are in the first month, 5, 6, 7, 8, 9 are in the second month and 10, 11, 12, 13 are in the third month – the same applies for every quarter (your calendar may be different of course). So with this in mind, I added a column and put an IF statement that basically looks at weeks 1, 2, 3, 4 in the quarter only – it ignores weeks 5 through 13 totally. It puts a 1 in the MonthID Column for the first quarter, and then a 4 in the MonthID column of the second quarter, a 7 in the MonthID of the third quarter and so on. So this step is adding the month number for the first month of every quarter and ignores the second and third months.
This is the code it generated for me
Then I needed to repeat this process for the second and third months in each quarter, but this time I didn’t need to add a new column. So I had to work out how to hand write this line of code – my Power Query Language skill is not that good. So I used a little trick to get the Power Query UI to write the basic code for me, and then I could modify it for my needs. First I selected my column from the previous step that contained either numbers or null values, and then from the Power Query UI I selected Transform\Replace Values and selected to replace null with the number 2 (this is an arbitrary value that is not already in the list).
Using the UI in this way created the following line of code – so I didn’t have to write it. All I had to do then is edit this line of code and replace the 2 that I added in the wizard (ie the “replace with” value) with another IF statement like the one above.
So I literally just copied the IF statement from my previous step, pasted it in place of the number 2, and modified it to act correctly on weeks 5 through 9.
I just repeated the process for weeks 10 through 13.
Now I had the monthID, creating the Fin Month Number was the same process as creating the Week of Quarter, just using different values.
Same again for Fin Week Number
The Calendar week numbers are a simple integer shift from the financial week numbers. But I needed to use an IF statement because some of the numbers had to be shifted up, and some were shifted down.
Same with Calendar Month Number
Once I had the calendar month number in hand, I could create the month names. In this case the year and day don’t matter as I am only extracting the month name. There may be a better way to do this, but this works.
Lastly I added a Day Name column
This is what the table now looks like
Now I know there is an issue in that sooner or later there will be a 53 week year. I haven’t dealt with this issue in my query, nor will I need to for a couple of years yet 🙂
Interested in Learning How to Do this Kind of Thing?
Our New Online Power Query Course just launched this year. You’ll learn Power Query from the best in the business, two Excel MVPs Ken Puls and Miguel Escobar. We’ve included 7 Hours of Self-Paced Video over 31 Courses, with lots of example files, and a Digital Badge powered by Acclaim.
CLICK HERE to claim your spot.
Time to make a function
I then took a copy of the query and turned this new copy into a function. I took a copy so I didn’t accidentally mess up the work I had done already (which I have done before and it is hard to recover if you are new to the coding Power Query Formula Language).
To create a function, you need to add the code to accept the input parameters (1 and 2 below) and then replace the 2 references to the start date in the code with the parameter “YearStartDate” (3 and 4 below)
So it went from this
To this
After you save the function, you get the Invoke Function screen
Press the Invoke button and you get prompted for a date.
Enter 29/4/2013 and press OK. There you have your calendar. It works for any year not withstanding the 53 week issue I haven’t dealt with yet. And it automatically stops at yesterday’s date preventing calendar over run.
Time to Make a Weekly Calendar
Now I have done all this work, I thought I would make a week calendar too. It is easy to do this using the day calendar I created. Just right click on the query workbook and select reference. This creates a new query linked to the first one.
I edited this new query to filter out all dates except Sundays (the last day of the week).
Finally I
- removed the day column
- renamed the Date Column to be called Week Ending
- removed the ID column (it now reads 7, 14, 21 etc)
- Renamed the Week ID column to be called ID
And there I had a week version of the calendar as a bonus (I use week calendars all the time).
Last Step – create a template.
I gave the 2 function driven calendars names and loaded both to the data model. I kept the script version and disabled loading – I might need it later.
Then a file\Save As\Excel Template
Now when I want to create a new Excel workbook from scratch, just go to File\New\Personal and select the template from the list.
The 2 date tables are already in the new workbook – just delete the one you don’t want from within Power Pivot. And that’s it. You never have to worry about updating your calendars again, and don’t have to worry about the end date over running.
Here is a copy of the workbook I created. I have the calendar tables loaded to the worksheet for demo purposes only, I only have it loaded to the Data Model in real life. I recommend M is for Data Monkey by Ken Puls and Miguel Escobar. I have written a review about this book here.
Matt Allington is a Professional Power Pivot Consultant based in Sydney Australia.
