powerbidaxisoweek-number

ISO 8601 Year- and Week numbers

I'm struggling with week sorting. In details: Measure for calculate weeks:

Year Week Column: =
CONCATENATE(
    YEAR ([Date]),
    " W" & FORMAT( WEEKNUM ([Date], 21), "00" )
)

switch_years

I cannot find a way to put 2023 W52 between 2022W52 and 2023 W01. Can you advise me in this subject? What should be the key to sort these weeks properly?

Thanks in advance!

Solution

  • It's not a matter of sorting. It's a matter of properly applying the ISO week calendar logic, see Wikipedia. Your formula for Year Week Column needs to consider that 2023-01-01 belongs to the ISO week year 2022 and here's how to calculate it:

    Year Week Column = 
IF (
    WEEKNUM ( [Date], 21 ) = 1
        && MONTH ( [Date] ) = 12,
    YEAR ( [Date] ) + 1,
    IF (
        WEEKNUM ( [Date], 21 ) >= 52
            && MONTH ( [Date] ) = 1,
        YEAR ( [Date] ) - 1,
        YEAR ( [Date] )
    )
) 
& "-W" & FORMAT ( WEEKNUM ( [Date], 21 ), "0#" )

    enter image description here

    With a correct Year Week Column you don't have any issues with sorting!