445 Calendar Works great until the 11 Month 2019

[Anonymous]

I followed the step in a Matt Allinton lesson on creating a custom 445 calendar .  The calendar works great for my company unitl you get to the 11 month of 2019.  In November 2019 the last 2 months of my companies ERP software jumps to 4-5-5 then back to 4-4-5.

How can I accomplish this?

[mahoneypat · Accepted solution]

Fiscal calendars are always fun to figure out.  Of course, you could hard code it in Excel and bring that in for your Date table, but that isn't very elegant.  What other years have the same behavior?  What is the logic for determining years in which November has 5 fiscal weeks?  Is it any year in which November has 5 Saturdays (2013, 2014, 2019, 2024, 2025)?

Pat

[mahoneypat · Accepted solution]

Fiscal calendars are always fun to figure out.  Of course, you could hard code it in Excel and bring that in for your Date table, but that isn't very elegant.  What other years have the same behavior?  What is the logic for determining years in which November has 5 fiscal weeks?  Is it any year in which November has 5 Saturdays (2013, 2014, 2019, 2024, 2025)?

Pat

[Anonymous]

I do have an Exel spreadsheet that I am using. I was hoping to use something a little nicer. I was also hoping that if I could figure out how to handle these first problem months, I would be able to apply it to other months also.  Thank You for your response.

[mahoneypat]

I kept thinking about this one and think I found a way to do it fairly dynamically.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.  Rather than try to come up with logic to derive the right fiscal week from a list of dates, I make the fiscal table first and then simply adding the list of dates to it.  The code contains two key comments in the Source and ListOfDates steps.  You can hit the gear icon in the Source step to see the small table I made for years 2018 to 2020.  You should be able to add more years to that table as needed, and then just update the #date() in the ListOfDates steps for the first day of the first fiscal year you've entered.

Please let me know if this works out for you, as I plan to make write a blog/video about it if so (after I refine it a bit further).

let
    // Input Table of Years and # of Weeks
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFDSUTI1UorVAfMsQTxjKM/IACoXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Weeks = _t]),
    #"Changed Type4" = Table.TransformColumnTypes(Source,{{"Weeks", Int64.Type}, {"Year", Int64.Type}}),
    #"Added Custom" = 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(#"Added Custom", "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}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "WeekInFM", each {1..[NumWeeks]}),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom1", "WeekInFM"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"WeekInFM", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "FW_Index", 1, 1, Int64.Type),
    #"Added Custom2" = Table.AddColumn(#"Added Index", "WeekDay", each {1..7}),
    #"Expanded WeekDay" = Table.ExpandListColumn(#"Added Custom2", "WeekDay"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded WeekDay",{{"WeekDay", Int64.Type}}),
    FiscalTable = Table.RemoveColumns(#"Changed Type2",{"Weeks", "NumWeeks", "WeekDay"}),
    // Choose correct start date based on first day of first fiscal year entered in Source step
    ListOfDates = List.Dates(#date(2017,12,31), List.Count(FiscalTable[Year]), #duration(1,0,0,0)),
    MakeTable = Table.FromColumns(Table.ToColumns(FiscalTable)&{ListOfDates}, {"FY","FM","WeekInMonth","FW_Index","Date"}),
    #"Changed Type3" = Table.TransformColumnTypes(MakeTable,{{"Date", type date}})
in
    #"Changed Type3"

Pat

[Anonymous]

Pat, This is awesome. I would love to read your blog and watch your video on this topic. 

Could I get a link to them?

Thank You so much

[mahoneypat]

Here's the link to the blog article, which includes a link to the YouTube video.

https://hoosierbi.com/2021/07/04/445-calendar-with-53-week-years/

Pat

[Anonymous]

Pat, How would I add fiscal week number in year and Fiscal Year, Month and Quarter start and end dates to this?

[mahoneypat · Accepted solution]

Glad to hear it worked for you.  I plan to write it this weekend, and will post the link here when published.  Please mark it as the solution, if so.

Pat