DAX Weeknum

[Anonymous]

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,

[Tahreem24 · Accepted solution]

@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.

[Greg_Deckler]

@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"