Forum Discussion
DAX Weeknum
hi everyone,
I want to create a measure to calculate the calendar weeks. The table looks like this:
| year | month | date |
| 2020 | January | 1 |
| 2021 | March | 18 |
| 2019 | July | 6 |
| 2021 | September | 22 |
I tried to aggregate the data first and then use weeknum, but it did not work.
Thank you in advance,
Anonymous - I tried to generate the solution using DAX.
Step 1: Create Calcualting Column like this:
Final Date =VAR Month = SWITCH('Date'[Month],"January",1,"Febuary",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)RETURN DATE('Date'[Year],Month,'Date'[Day])Step 2: Create another column to get the weeknum:Week Number = WEEKNUM('Date'[Final Date])I am also attaching PBIX for your reference.Hope it may help you.
2 Replies
Anonymous I would recommend adding a true date column in Power Query and then WEEKNUM will work:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUfJKzCtNLKoEsgyVYnXAwoZAjm9iUXIGSNACKmpoCVJcmgNSaYasMji1oCQ1Nym1CMg2MlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [year = _t, month = _t, date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"year", type text}, {"month", type text}, {"date", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [month] & " " & [date] & ", " & [year]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}) in #"Changed Type1"Anonymous - I tried to generate the solution using DAX.
Step 1: Create Calcualting Column like this:
Final Date =VAR Month = SWITCH('Date'[Month],"January",1,"Febuary",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)RETURN DATE('Date'[Year],Month,'Date'[Day])Step 2: Create another column to get the weeknum:Week Number = WEEKNUM('Date'[Final Date])I am also attaching PBIX for your reference.Hope it may help you.
