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.