445 calendar week number to reset each month

[ArnaudCamp]

Hi everyone,

Hope you are well,

I have build up a 445 calendar starting 30/12/2021.

I would like to add a WeekOfMonth column with the 1st week starting on the 30/12, then 2nd, 3rd, 4th and resetting to 1 the following month.

I worked and looked as much a possible to find a solution but no success.

Anyone would have an idea how to proceed?

Thank you in advance 

[Vijay_A_Verma · Accepted solution]

Use this in a custom column

= [n=Number.Mod(Number.IntegerDivide(Duration.Days([Date]-#date(2021,12,30)),7),13),
MonthWeekNum=if n=12 then 5 else Number.Mod(n,4)+1][MonthWeekNum]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XczBCcAwDEPRXXIu2JLSZpiQ/ddoCi22e3ygrzkbaHKjE20dm3Y+4IsrA8qiF5UlR2j/Z8CLypA9pJypZCqZ/hlC3UYGvuG6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month Week Number 445", each [n=Number.Mod(Number.IntegerDivide(Duration.Days([Date]-#date(2021,12,30)),7),13),
MonthWeekNum=if n=12 then 5 else Number.Mod(n,4)+1][MonthWeekNum], type number)
in
    #"Added Custom"

[ArnaudCamp]

Hi Vijay,

Thank you so much, this is brilliant.

It works perfectly.

Genius.

Thank you again,

Arnaud