Creating a 4-4-5 Calendar with Power Query
4-4-5 calendar is a common calendar structure for some industries such as retail and manufacturing. You will need to create such a calendar if you wish to harness the full power of DAX measures in order to analyze data following this calendar structure.
Edit 30-Jan-2020: Please check this post for a 4-5-4 Calendar: https://xcelanz.com/creating-4-5-4-calendar-with-power-query/ Power Query 4-4-5 calendar
You can download the excel file HERE
The M code I am sharing is one I have created which allows me to quickly and dynamically create such a calendar whenever I need it. It can be easily adapted to a 4-5-4 or 5-4-4 type of calendar.
The only part that you will need to modify in order to adapt it to your analysis is the following (at the very end of the code):
CreateWholeCalendar(#date(2018,6,29),2019,5)
where:
- #date(2018,6,29): is the first day of the first period of the first fiscal year you want to create the calendar for
- 2019: is the first fiscal year
- 5: is the number of fiscal years you want to create
Copy paste the below M code into your PQ advanced editor, then add it to your model and WOW your boss with some cool 4-4-5 analysis 🙂 and of course, let me know if this has helped you or if there are things you’d improve or add!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | let CreateCustomCalendar = (StartDate as date, FiscalYear as number, FiscalYearSequence as number) as table => let InitialDates = Table.FromList( List.Dates(StartDate,364, #duration(1,0,0,0)), Splitter.SplitByNothing(), type table [Date=Date.Type], null, ExtraValues.Ignore), // split dates into 2 tables // one containing periods 1,2 then 4,5 then 7,8 and 10,11 each one of 28 days // and the other containing periods 3,6,9,12 each one of 35 days Tbl28 = Table.AlternateRows(InitialDates,56,35,56), Tbl35 = Table.AlternateRows(InitialDates,0,56,35), Tbl28AddIndex = Table.AddIndexColumn(Tbl28,"Indx",0,1), Tbl28AddInt = Table.AddColumn(Tbl28AddIndex, "FiscalPeriodNumber", each Number.IntegerDivide([Indx],28) + Number.IntegerDivide([Indx],56) + 1 , Int64.Type), Tbl35AddIndex = Table.AddIndexColumn(Tbl35, "Indx",0,1), Tbl35AddInt = Table.AddColumn(Tbl35AddIndex,"FiscalPeriodNumber", each (1+Number.IntegerDivide([Indx],35)) * 3, Int64.Type), CombineTbl = Table.SelectColumns(Table.Sort(Table.Combine({Tbl28AddInt,Tbl35AddInt}), {"Date",Order.Ascending}),{"Date","FiscalPeriodNumber"}), // add additional Fiscal info columns AddPeriodLabel = Table.AddColumn(CombineTbl, "FiscalPeriodLabel", each "P" & Text.From([FiscalPeriodNumber]), type text), AddFiscalPeriodSequence = Table.AddColumn(AddPeriodLabel, "FiscalPeriodNumberSequence", each [FiscalPeriodNumber] + (FiscalYearSequence - 1) * 12, Int64.Type), AddFiscalQuarter = Table.AddColumn(AddFiscalPeriodSequence, "FiscalQuarterNumber", each Number.IntegerDivide([FiscalPeriodNumber]-1,3)+1, Int64.Type), AddFiscalQuarterLabel = Table.AddColumn(AddFiscalQuarter, "FiscalQuarter", each "Q" & Text.From([FiscalQuarterNumber]), type text), AddFiscalQuarterSequence = Table.AddColumn(AddFiscalQuarterLabel, "FiscalQuarterSequence", each [FiscalQuarterNumber] + (FiscalYearSequence - 1) * 4, Int64.Type), AddFiscalYear = Table.AddColumn(AddFiscalQuarterSequence,"FiscalYear", each FiscalYear, Int64.Type), AddDayNumberOfFiscalYear = Table.AddIndexColumn(AddFiscalYear,"DayNumberOfFiscalYear",1,1), AddDayNumberOfFiscalYearSequence = Table.AddColumn(AddDayNumberOfFiscalYear, "DayNumberOfFiscalYearSequence", each [DayNumberOfFiscalYear] + (FiscalYearSequence - 1) * 364, Int64.Type), AddFiscalWeekNum = Table.AddColumn(AddDayNumberOfFiscalYearSequence,"FiscalWeekNumber",each Number.IntegerDivide([DayNumberOfFiscalYear]-1,7)+1, Int64.Type), AddFiscalWeekLabel = Table.AddColumn(AddFiscalWeekNum, "FiscalWeekLabel", each "Week " & Text.From([FiscalWeekNumber]), type text), AddFiscalWeekNumSequence = Table.AddColumn(AddFiscalWeekLabel, "FiscalWeekNumberSequence", each [FiscalWeekNumber] + (FiscalYearSequence - 1) * 52, Int64.Type), // add Calendar Info columns AddCalendarWeek = Table.AddColumn(AddFiscalWeekNumSequence, "CalendarWeek", each Date.WeekOfYear([Date],Day.Sunday), Int64.Type), AddCalendarWeekLabel = Table.AddColumn(AddCalendarWeek, "CalendarWeekLabel", each "Week " & Text.From([CalendarWeek]), type text), AddMonthNumber = Table.AddColumn(AddCalendarWeekLabel, "MonthNumberOfCalendarYear", each Date.Month([Date]), Int64.Type), AddMonthName = Table.AddColumn(AddMonthNumber, "MonthName", each Date.MonthName([Date]), type text), AddWeekDayNumber = Table.AddColumn(AddMonthName, "DayNumberOfWeek", each Date.DayOfWeek([Date], Day.Monday) + 1, Int64.Type), AddWeekDayName = Table.AddColumn(AddWeekDayNumber, "WeekdayName", each Date.ToText([Date],"dddd"), type text), AddCalendarQuarter = Table.AddColumn(AddWeekDayName, "QuarterOfCalendarYear", each Date.QuarterOfYear([Date]), Int64.Type), AddCalendarQuarterLabel = Table.AddColumn(AddCalendarQuarter, "QuarterLabelOfCalendarYear", each "Q" & Text.From([QuarterOfCalendarYear]), type text), AddCalendarYear = Table.AddColumn(AddCalendarQuarterLabel, "CalendarYear", each Date.Year([Date]), Int64.Type) in AddCalendarYear, CreateWholeCalendar = (InitialDate as date, InitialFiscalYear as number, NumberOfCalendars as number) as table => Table.Combine(List.Accumulate({1..NumberOfCalendars}, {}, (state,current) => state & {CreateCustomCalendar(Date.AddDays(InitialDate, (current -1) * 364), InitialFiscalYear + current - 1, current)} )) in CreateWholeCalendar(#date(2018,6,29),2019,5) |
Must read books if you want to become a Power Query guru:
This is great, i need to edit to a 5-4-4 calendar but my result is continually off. Do you happen to have the code for 5-4-4 version, or point me to the part of the code i need to adjust? Thanks!
Hi Sarah – thanks for your feedback.
The part to change would be the one below:
Tbl28 = Table.AlternateRows(InitialDates,56,35,56),
Tbl35 = Table.AlternateRows(InitialDates,0,56,35)
Essentially for Tbl28 we are saying: take the first 56 (two times 4 weeks, 4-4) rows of the table InitialDates, then skip 35 rows (5 weeks, 5) and then keep the next 56 rows (two times 4 weeks). And repeat the last two steps for all the table.
Tbl35, on the other hand, takes no amount of initial rows, skip the first 56 rows (two times four weeks, 4-4) and keeps the next 35 rows (5 weeks, 5). And repeat the last two steps for all the table.
Therefore, in order to convert it into a 5-4-4 version what we would need to do is:
Take the first 35 rows, skip the next 56 rows and keep the next 35 tows. Tbl35 becomes:
Tbl35 = Table.AlternateRows(InitialDates,35,56,35)
and Tbl28 becomes:
Tbl28 = Table.AlternateRows(InitialDates,0,35,56),
Please do test it on your side and let me know if it works!
Hi,
How do we implement the 4-5-4 calendar? Haven’t been able to get it right.
Thanks in advance!
Hello Veena! I have just published a new post about this. Please check: https://xcelanz.com/creating-4-5-4-calendar-with-power-query/
Hi brilliant bit of Code do you know how you can get the fiscal period for more than one year? my years run for 10 years for example
Hi Craig,
if I understand correctly, you would just need to update the very last line of the code, i.e.:
CreateWholeCalendar(#date(2018,6,29),2019,5)
where:
#date(2018,6,29): is the first day of the first period of the first fiscal year you want to create the calendar for
2019: is the first fiscal year
5: is the number of fiscal years you want to create
You are a GOD!
Thank you Joe. I am glad you found it helpful!
Hi Livio,
I just tried this out and it was great, but I ran into this problem
(1) I used your code for 2017 to 2019. However, I noticed that the 2017 fiscal year ended on 12/30/2017 and the 2018 fiscal year started on 12/31/2017. In our company the 365th day of the year gets added to the last fiscal month. Is there a way to adjust the code to handle these end of year rounding issues. Our fiscal year is still 365 days (or 366 days in leap year).
(2) Another idea or suggestion is to include offsets.
I’m new to PowerQuery/PowerPivot, but I watched Avi Singh talk about offsets and think they sound like a great idea. His video is below…
https://www.youtube.com/watch?v=XjVLaVLluYE&t=416s
Hi Bill – thanks for the feedback!
4-4-5 calendars are characterized by having 364 days for each year therefore it does not coincide with a normal calendar.
If I understood correctly, you are looking to have the following calendar (which is not a pure 4-4-5):
Quarter 1, 2 and 3:
4 (weeks) – 28 days
4 (weeks) – 28 days
5 (weeks) – 35 days
Quarter 4:
4 (weeks) – 28 days
4 (weeks) – 28 days
5 (weeks) + 1 day (or 2 days if leap year) – 36 days
Is this correct?
HI Livio,
I have the same question as Bill asked in (1).
A calendar with
Quarter 1, 2 and 3:
4 (weeks) – 28 days
4 (weeks) – 28 days
5 (weeks) – 35 days:
Quarter 4:
4 (weeks) – 28 days
4 (weeks) – 28 days
5 (weeks) + 1 day (or 2 days if leap year) – 36 days
is what I would like to know how to do.
It would help tremendously if you would provide a revised version of your code with this modification.
Hi Livio,
Thank you for the code that you provided. I’ve been using it, but I ran into the same problem as Bill. I want a fiscal calendar like this one that you described in your reply to Bill, with the days added to Q4, How do I make that? Also, if you would provide updated code, that would help a lot!
Hi Jonathan, I will try to post a code for your scenario. This should be easier to do than creating a 4-4-5 calendar
Sorry for the late ask on this, but did you ever post this? I’m in the same boat.
Is there an update to this thread? I would also find this very useful
This is awesome. Just started working for a company that uses 445, and this will be very helpful. One question – Our company uses the 445 throughout the year, but always ends the year on 12/31, regardless of how the days fall on the 445. Is there a way to incorporate that into this code?
Hi David – see my response to Bill’s comment. Is your scenario the same?
Yes! I guess it is!
Hi – this is some impressive code. I’m just trying to adjust the output a little bit but am struggling. It seems like I’m off by just a couple days in each fiscal month (period). I’m trying to replicate the following (for 2019).
Can you advise what I would change in your code, please? I guess I’m not too clear on your use of the index column…
Thank you,
Mike
Start End
January Jan-01 Jan-26
February Jan-27 Feb-23
March Feb-24 Mar-30
April Mar-31 Apr-27
May Apr-28 May-25
June May-26 Jun-29
July Jun-30 Jul-27
August Jul-28 Aug-24
September Aug-25 Sep-28
October Sep-29 Oct-26
November Oct-27 Nov-23
December Nov-24 Dec-31
Hi Mike! First I want to thank you for the feedback provided.
Now, looking at your question, I am not sure that your days pattern should follow a 4-4-5 calendar. A 4-4-5 calendar is formed by a pattern of 28days-28days-35days, but yours is 27-27-34, and not for all the segments, in fact the first segment is 25 days and the last is 37. Besides a 4-4-5 calendar does not coincide with a normal calendar whereas yours does (the year always starts on January 1st). In your case I think the solution would be simpler by just hard coding the segments for each year, unless it varies by year?
This is pretty amazing. I seem to be running into an issue where one of the years has 53 weeks, so my current fiscal dates are a week off. Is there a way to adjust for this?
Hi Eddie – can you show me an example of how you’re using the function? Probably what you’re after is not a pure 4-4-5 calendar?
That is natual in 4-4-5 where over the course of 5-6 years, one year will have 53 weeks vs 52 weeks. I guess the only way to account for this to add a whole bunch of logic for the last line where if the user inputs more than “5” years of a calendar build, it has to adjust a week.
I am trying to use this calendar where our 4,4,5 calendar runs from jan-dec. I amended the code to start 1/1/2018 and run for 5 years and it works but 2020 should have 53 weeks but it only has 52. is there a way to adjust for this? guess this is the reverse of Eddie’s problem.
Hi,
This is absolutely brilliant so thank you for making it, been trying to find a SQL version, but I’m just going to insert the output of this into a table.
The only issue I’m having is that within the Query the date is shown correctly as 21/10/2019 etc, but when I save and close the Date is converted to a number. Changing the column type to text fixes this issue, but I was wondering if there was a way to keep the column as a Date?
Hi Luke! Are you using this in Power BI? What is the data type of the column?
I’m just using the stock excel power query editor. As is usually the case with developing anything simply running it multiple times fixed the issues.
Livio,
I am having the same problem with our 53 week year. It happens every 7 years. So The first year starts on 10/1/2017 and fits your pattern well (52 Weeks) until 7 years later in 2024 where we add one week to the last period (it would end on 9/28/2024.
Would that be easy to code?
Hi there, I am also interested in the response to the 53 week request.
Your code works brilliantly, but this variation to the 4-4-5 seems fairly common