Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

WEEKNUM returns false calendar weeks

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's avatar
    dedelman_clng
    5 years ago

    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

6 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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!

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        dedelman_clng
        Community Champion
        5 years ago

        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