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-09-13", "2016-09-17" },
                { 2016, "2016-09-18", "2017-09-16" },
                { 2017, "2017-09-17", "2018-09-15" },
                { 2018, "2018-09-16", "2019-09-14" },
                { 2019, "2019-09-15", "2020-09-12" },
                { 2020, "2020-09-13", "2021-09-18" },
                { 2021, "2021-09-19", "2022-09-17" }
 
What I have is very much based on quarters and can only compute 454 445 or 544 quarters. I don't need quarters just 13 periods and week beginning and week end;
thanks in advance
  • 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

     

     

6 Replies

  • keekee's avatar
    keekee
    Frequent Visitor

    HI, many thanks for your response, I keep getting the error:

    This visual contains one or more filters with deleted columns, type mismatches, or other breaking modelling changes

  • keekee's avatar
    keekee
    Frequent Visitor

    DAX would be great; I do have a calander that I inherited from a previous employee but it is totally based on the 544 454 455 structure;

    thanks again

  • keekee's avatar
    keekee
    Frequent Visitor

    HI, thanks so much for your help - I did get the Query working in the end and its nearly there but I am missing my 53 rd week where I need it;

    my time periods are as below

    e.g. 2017 I need to start the year on 17th September not the 10th and finish on the 14th September.

    Many thanks again

     

       { 2016"2016-09-18""2017-09-16" },
                    { 2017"2017-09-17""2018-09-15" },
                    { 2018"2018-09-16""2019-09-14" },
                    { 2019"2019-09-15""2020-09-12" },
                    { 2020"2020-09-13""2021-09-18" },
                    { 2021"2021-09-19""2022-09-17" }
    • ppm1's avatar
      ppm1
      Solution Sage

      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