WEEKNUM returns false calendar weeks

[Anonymous]

Hello, 

I am facing the following issue: 

I am trying to calculate the calendar weeks with WEEKNUM() and have the following errors with the two different variants of the function:

-Calendarweek=CONCATENATE(YEAR([date]), CONCATENATE(" - ", WEEKNUM([date])))

--> in this case it returns "2021-3" for the current week (starting on the 11th of January 2021). 

--> This is not correct, as it should return "2021-2".

--> Then I tried to mitigate this and built in the "21" in the following function:

-Calendarweek=CONCATENATE(YEAR([date]), CONCATENATE(" - ", WEEKNUM([date],21)))

--> in this case it returns "2021-53" for the 1st of January until the 3rd of January 2021

--> This is clearly not correct!

How should I solve this problem?

Thank you in advance.

[dedelman_clng]

@Anonymous - what do you expect the value for WEEKNUM for 1 Jan thru 3 Jan to be?

[Anonymous]

Hi! I'd like to have the 1st, 2nd and 3rd of January 31 still part of 2020 - 53.

How can I build this in?

[dedelman_clng · Accepted solution]

@Anonymous  - I found a formula for ISO Year from a previous post. This should get you what you want.

CalendarWeek =
VAR __Wk =
    WEEKNUM ( DateTab[Date], 21 )
VAR __ISOYr =
    IF (
        AND ( WEEKNUM ( DateTab[Date], 21 ) < 5, WEEKNUM ( DateTab[Date], 2 ) > 50 ),
        DateTab[Year] + 1,
        IF (
            AND ( WEEKNUM ( DateTab[Date], 21 ) > 50, WEEKNUM ( DateTab[Date], 2 ) < 5 ),
            DateTab[Year] - 1,
            DateTab[Year]
        )
    )
RETURN
    __ISOYr & "-" & __Wk

Hope this helps

David

[Anonymous]

Fantastic! It worked!! Thank you. 🙂

[dedelman_clng]

Hi @Anonymous  - 

Using WEEKNUM without a second value (as in your first attempt) defaults to Sunday as the first day of the week. 

Try WEEKNUM([date], 2) for weeks beginning on a Monday.

There is some explanation behind the use of 21 at this link: https://dax.guide/weeknum/ 

Hope this helps

David

[Anonymous]

Hi David, 

Thank you for replying and your help! 

Unfortunately it still returns 2021-3 for the current week. 

How should I proceed?

Thank you in advance!