Date.Week of year

[Anonymous]

I follwed this great tutorial from Pat Mahoney

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

to get this 455 fiscal calendar.  My problem now is adding the WeekInFiscalYear.

I have tried to add a column  using Date.WeekOfYear([Date]) but it gives me the calendar week number . I need the WeekInYear to coincide with the this 445 calendar.

Thank You

[Anonymous]

Hi @AlexisOlson,

Did AlexisOlson's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements to find it more quickly.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[AlexisOlson · Accepted solution]

You could subtract the minimum FW_Index for the year from the current FW_Index.

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),
    Add7DayListPerWeek = Table.AddColumn(AddWeekIndex, "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),
    // New code starts here
    #"Grouped Rows" = Table.Group(AddDatesBasedOnStartDateAndDayIndex, {"Year"}, {{"MinFW", each List.Min([FW_Index]), Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(AddDatesBasedOnStartDateAndDayIndex, {"Year"}, #"Grouped Rows", {"Year"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"MinFW"}, {"MinFW"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "WeekInYear", each [FW_Index] - [MinFW] + 1, Int64.Type)
in
    #"Added Custom"