Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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"
Hi Vijay,
Thank you so much, this is brilliant.
It works perfectly.
Genius.
Thank you again,
Arnaud
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.