Forum Discussion

keekee's avatar
keekee
Frequent Visitor
3 years ago
Solved

Fiscal Calendar

HI, I need some help to create a Financial Calander ; I need 13 Periods for each year. Most have 4 weeks and for 2 of my years the 13th Period has 5 weeks; these are the year ranges: { 2015, "2015...
  • ppm1's avatar
    ppm1
    3 years ago

    I modified the code from this article to create your needed Date table. The min/max dates seem to match what you listed.

    445 Calendar with 53-Week Years – Hoosier BI

     

    let
    
      // Enter the date in Advanced Editor for the first day of the earliest Fiscal Year in #date(yyyy,m,d) format                       
      StartDate = #date(2015, 9, 13),
      // Enter the desired range of years as List in Advanced Editor                                                              
      YearRange = {2016 .. 2021},                                                                               
      YearsWith53Weeks = {2015, 2020},                             
      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 .. 13}, {4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5}})
          else
            List.Zip({{1 .. 13}, {4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4}})
      ),
      #"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

     

    Pat