Fiscal Calendar

[keekee]

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

[keekee]

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 · Accepted solution]

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

[keekee]

thanks so much; have a great day

[keekee]

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]

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

[Greg_Deckler]

@keekee Here is a Power Query solution: 13 Period Fiscal Calendar (Power Query M function) - M Code Showcase - Enterprise DNA Forum

If that doesn't work for you I can try to find something or create something with DAX.