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),
  AddFWinYear = Table.AddColumn(
    AddWeekIndex,
    "FWinYear",
    each
      let
        thisyear = [Year],
        thisindex = [FW_Index],
        result = Table.RowCount(
          Table.SelectRows(AddWeekIndex, each [Year] = thisyear and [FW_Index] <= thisindex)
        )
      in
        result
  ),
  Add7DayListPerWeek = Table.AddColumn(AddFWinYear, "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.

5 Comments

  1. Awesome, article. A colleague and I used your code to make exactly what we needed. First time for me that I had to deal with this type of calendar. Very happy to have found it. Thank you so much.

  2. Me, again, I'm having a hard time with something. I would need an Week Index (1 to 52(53) for each week, that resets to 1 for every year.
    This is to be able to do a comparison between 2 years.

    Example compare week #32 in 2019 to week 32 in 2020
    2019-32 500$
    2020-32 600$
    Diff of 100$ But I don't see how could compare 2 periods ? Thanks

    1. You aren't the first to ask for that. I updated the code in the article to include a FWinYear column that resets every year. Please let me know if other changes would be helpful.

      1. Awesome, thank you. This is the Ultimate 445 Calendar !

        I was able to before your modification to your script to generate what I needed by modifying (my colleague did that) and I made a step 2 Power BI to load the output of Step 1 and make the week (52-53) index on that one.

        I would like to thank you for sharing your script, it really solved my problem. First time in 20 years I needed a 445 calendar. The projet I'm working on that's what was needed and your web page was the key to solving the problem that I had.

        Thanks again for sharing this solution

        Eric (Montreal, Canada)

  3. Hi Pat,

    I have done some workaround to create code for 4-5-5 calendar with 53-week years but the result was not expected. It would be more helpful and grateful if you could provide the code for the same. Thanks in advance for your support/hard work!

    Regards,
    Surendra

Leave a Reply

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