Creating 5-4-4 Calendar with Power Query
This post is a continuation of the my previous post about how to create a 5-4-4. Please read here on how to use this function: Creating a 4-4-5 Calendar with Power Query
Please check this post for a 4-5-4 Calendar: Creating 4-5-4 Calendar with Power Query
The M code I am sharing is one I have created which allows me to quickly and dynamically create a 5-4-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 | 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 2,3 then 5,6 then 8,9 and 11,12 each one of 28 days // and the other containing periods 1,4,7,10 each one of 35 days Tbl28 = Table.AlternateRows(InitialDates,0, 7*5, 7*4 + 7*4), Tbl35 = Table.AlternateRows(InitialDates, 7*5, 7*4 + 7*4, 7*5), Tbl28AddIndex = Table.AddIndexColumn(Tbl28,"Indx",0,1), Tbl28AddInt = Table.AddColumn(Tbl28AddIndex, "FiscalPeriodNumber", each Number.IntegerDivide([Indx],28) + Number.IntegerDivide([Indx],56) + 2 , Int64.Type), Tbl35AddIndex = Table.AddIndexColumn(Tbl35, "Indx",0,1), Tbl35AddInt = Table.AddColumn(Tbl35AddIndex,"FiscalPeriodNumber", each Number.IntegerDivide([Indx],35) * 3 + 1, 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(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!