Creating 4-5-4 Calendar with Power Query
This post is a continuation of the my previous post about how to create a 4-4-5. Please read here on how to use this function: https://xcelanz.com/creating-a-4-4-5-calendar-with-power-query/
The M code I am sharing is one I have created which allows me to quickly and dynamically create a 4-5-4 calendar whenever I need it.
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 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | let CreateCustomCalendar = (StartDate as date, FiscalYear as number, FiscalYearSequence as number) as table => let InitialDates28 = Table.FromList( List.Dates(StartDate,364, #duration(1,0,0,0)), Splitter.SplitByNothing(), type table [Date=Date.Type], null, ExtraValues.Ignore), InitialDates35 = Table.FromList( List.Dates(Date.AddDays(StartDate, -28), 364, #duration(1,0,0,0)), Splitter.SplitByNothing(), type table [Date=Date.Type], null, ExtraValues.Ignore), // split dates into 3 tables // one containing periods 1, 4, 7, 10, EACH OF 28 DAYS // one containing periods 2,5,8,11 each of 35 days // one containing periods 3,6,9,12 each of 28 days Tbl281 = Table.AlternateRows(InitialDates28, 7*4, 7*5 + 7*4, 7*4), Tbl35 = Table.AlternateRows(InitialDates35,0, 7*4 + 7*4, 7*5), Tbl282 = Table.AlternateRows(InitialDates28, 0, 7*5 + 7*4, 7*4), Tbl281AddIndex = Table.AddIndexColumn(Tbl281,"Indx",0,1), Tbl281AddInt = Table.AddColumn(Tbl281AddIndex, "FiscalPeriodNumber", each Number.IntegerDivide([Indx],28) * 3 + 1, Int64.Type), Tbl35AddIndex = Table.AddIndexColumn(Tbl35, "Indx", 0, 1), Tbl35AddInt = Table.AddColumn(Tbl35AddIndex,"FiscalPeriodNumber", each Number.IntegerDivide([Indx],35) * 3 + 2, Int64.Type), Tbl282AddIndex = Table.AddIndexColumn(Tbl282,"Indx",0,1), Tbl282AddInt = Table.AddColumn(Tbl282AddIndex, "FiscalPeriodNumber", each Number.IntegerDivide([Indx],28) * 3 + 3, Int64.Type), CombineTbl = Table.SelectColumns(Table.Sort(Table.Combine({Tbl281AddInt,Tbl35AddInt,Tbl282AddInt}), {"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(2020,1,1),2020,5) |
Has this post helped you solve your problem? Please let me know in the comments and share with your friends!
Thank you very much!!! I have a question, On 445 calendar there are some years with 53 weeks. Do you have any solution for that years?
Than you again!
Hi Gabriel! This is a natural consequence of having 53 weeks sometimes since a business year spans 364 days. Perhaps what you are after is not a 445 calendar?
what if the year always starts on sunday?
Hi,
I love it and thanks a lot.
I have some questions related in the display (Fiscal and Calendar):
a) In my organisation, my week starts Friday to end on Thursday. In PBI when I display by day of week it comes alphabetic order. Ideally it will need to start on Friday, Sat, Sun… till Thursday. Any code I need to modify to fix it
b) When I display by Week or Period, I always have the issue with the Weeks 11-19 come between Week 1 and week 2. Will it be possible to have the weeks period displayed with a double digit as Week 01…etc
Thanks for your assistance