Week of Month based in the first Monday

[Postigo_]

Hello Everyone,

I'm facing some trouble when i try to calculate the week of month for each month always starting on the first monday of the month

Using this sample as a example, Start of the week is the column i'm trying to apply the logic, as you can see my week always starts on monday from 11/27/2023 until 12/3/2023 it will be the 5th week of November but when change to December i need to start as my 1st week, basically i have to do something like "Week of Month Monday"  

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
    #"Added Custom" = Table.AddColumn(Source, "EndDate", each Date.EndOfMonth(Date.From(DateTime.LocalNow())), type date),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"StartDate", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each [Dates] >= #date(2023, 11, 26) and [Dates] <= #date(2023, 12, 12)),
    #"Inserted Day Name" = Table.AddColumn(#"Filtered Rows", "Day Name", each Date.DayOfWeekName([Dates]), type text),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Day Name", "Week of Month(Dates)", each Date.WeekOfMonth([Dates]), Int64.Type),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Dates],Day.Monday), type date),
    #"Inserted Week of Month1" = Table.AddColumn(#"Inserted Start of Week", "Week of Month(Start of Week)", each Date.WeekOfMonth([Start of Week]), Int64.Type),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Week of Month1", "End of Week", each Date.EndOfWeek([Dates]), type date)
in
    #"Inserted End of Week"

Thanks

[AlienSx · Accepted solution]

Hello, @Postigo_ I give you an alternative solution. I did not want to start from your sample but generated dates myself. Besides your November week numbers (4 and 5) are incorrect. Key is to group dates by weeks of month. Here GroupKind.Local parameter works. But I had to cut first and last groups because first and last groups are not "full month". So initially go wider than you need to get good results in the middle.

One may use different approach to assign week numbers inside each month. I decided to go my way.

let
// generate dates table
    dates = #table(
        {"Dates"},
        List.Generate(
            () => #date(2023, 1, 1),
            each _ < #date(2025, 1, 1),
            each Date.AddDays(_, 1),
            each {_}
        )
    ),
// group dates by custom month start (first Monday), split dates by weeks, assign week numbers
    s = Table.Group(
        dates, "Dates", 
        {{"all", each List.Split(_[Dates], 7)},
        {"c", each {1..Number.IntegerDivide(Table.RowCount(_), 7)}}}, 
        GroupKind.Local,
        (s, c) => Byte.From(Date.DayOfWeek(c, Day.Monday) = 0 and Date.Month(s) <> Date.Month(c))
    ),
// cut first and last groups
    skip = Table.RemoveLastN(Table.RemoveFirstN(s, 1), 1),
// zip weeks and their numbers
    week_nums = Table.AddColumn(skip, "wn", each List.Zip({[all], [c]})),
// final touches
    combine = Table.FromRows(List.Combine(week_nums[wn]), {"d", "week_no"}),
    expand = Table.ExpandListColumn(combine, "d")
in
    expand

[Postigo_]

Hello @AlienSx , 

Thanks a lot for your quick answer, i did some tests here and this works perfectly !!