445 Calendar with 53-Week Years

Many businesses operate on a standard calendar so that each fiscal week/month/year begin and end on the same day of the week, making YOY comparisons easier. A 445 calendar is one example, in which each quarter is comprised of 13 weeks (4 + 4 +5), giving each year 52 weeks (364 days). This works well, but periodically a 53rd week is needed in the year to account for the 365th days. Power Query is a great way to make Date tables; however, dealing with the 53-week years can be a challenge.

Most solutions start with a list of dates (e.g., with the List.Dates() function), along with clever expressions to assign the correct fiscal week/month/year according to the 445 calendar. The approach shared here generates the Date table backwards, starting with the fiscal years needed, assigning it 52 or 53 weeks, and adding rows for the right number of weeks in each month. See the video below for a walkthrough of the M code.

Below is the M code that can be easily adapted for other standard calendars (see embedded comments for instructions). Note that the years with 53 weeks can be hard coded or assigned dynamically. There are two steps with the same name – YearsWith53Weeks. Each step must have a unique name, so one of them must be commented out. In this example, the years with 53 weeks are determined by selecting those years (with the List.Select() function) in which November 30th falls on Saturday.

let
    // Enter the date in Advanced Editor for the first day of the earliest Fiscal Year in #date(yyyy,m,d) format
    StartDate = #date(2017, 12, 31),
    // Enter the desired range of years as List in Advanced Editor
    YearRange = {2018..2021},
    // In Advanced Editor, use one of the two options for this step (code out the one not used with //) to either hard code or dynamically calculate the years with 53 weeks.  Substitute a different evaluation criterion as needed.
    // YearsWith53Weeks = {2019},
    YearsWith53Weeks = List.Select(YearRange, each Date.DayOfWeekName(#date(_,11,30)) = "Saturday"),
    StartingTable = Table.FromColumns({YearRange}, {"Year"}),
    AddNumberOfWeeksColumn = Table.AddColumn(StartingTable, "Weeks", each if List.Contains(YearsWith53Weeks, [Year]) then 53 else 52),
    #"Changed Type4" = Table.TransformColumnTypes(AddNumberOfWeeksColumn,{{"Year", Int64.Type}, {"Weeks", Int64.Type}}),
    // In the Advanced Editor, enter the two patterns for 52 and 53 week years as a list of weeks per fiscal month
    AddListOfMonthAndWeekCounts = Table.AddColumn(#"Changed Type4", "Custom", each if [Weeks] = 53 then List.Zip({{1..12}, {4,4,5,4,4,5,4,4,5,4,5,5}}) else List.Zip({{1..12}, {4,4,5,4,4,5,4,4,5,4,4,5}})),
    #"Expanded Custom" = Table.ExpandListColumn(AddListOfMonthAndWeekCounts, "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"FM", "NumWeeks"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"FM", Int64.Type}, {"NumWeeks", Int64.Type}}),
    AddListOfWeeksColumn = Table.AddColumn(#"Changed Type", "WeekInFM", each {1..[NumWeeks]}),
    #"Expanded Custom1" = Table.ExpandListColumn(AddListOfWeeksColumn, "WeekInFM"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"WeekInFM", Int64.Type}}),
    AddWeekIndex = Table.AddIndexColumn(#"Changed Type1", "FW_Index", 1, 1, Int64.Type),
    Add7DayListPerWeek = Table.AddColumn(AddWeekIndex, "WeekDay", each {1..7}),
    #"Expanded WeekDay" = Table.ExpandListColumn(Add7DayListPerWeek, "WeekDay"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded WeekDay",{{"WeekDay", Int64.Type}}),
    RemoveUnneededColumns = Table.RemoveColumns(#"Changed Type2",{"Weeks", "NumWeeks", "WeekDay"}),
    AddDayIndex = Table.AddIndexColumn(RemoveUnneededColumns, "DayIndex", 0, 1, Int64.Type),
    AddDatesBasedOnStartDateAndDayIndex = Table.AddColumn(AddDayIndex, "Date", each Date.AddDays(StartDate, [DayIndex]), type date)
in
    AddDatesBasedOnStartDateAndDayIndex

I hope this M code to helpful to you. Please leave comments below with any feedback/questions.

You May Also Like

About the Author: Pat Mahoney

Pat Mahoney is a chemist in Indiana who enjoys teaching others to use Power BI and other tools, and providing pro bono data science help/instruction to nonprofits, schools, etc.

Leave a Reply

Your email address will not be published. Required fields are marked *