Forum Discussion

lilianzhwei's avatar
lilianzhwei
New Member
6 years ago
Solved

BUG ISO 8601

Hi, 

I tried use order the date to Year-WeekNum. But found a bug here. Do you also have the same problem?

Many thanks & Br,

Lilli

 

CodeBug

 
Need Help
  • d_gosbell's avatar
    d_gosbell
    6 years ago

    Actually I just found that dax.guide has the 21 option listed even though the official docs doesn't. If you are using true ISO8601 weeknum then the week at the end of the year will be "pulled" into the next year if it has 3 days or less. And the reverse happens too - if the first week in Jan has 3 days or less those days are considered as part of the prior year. You will probably need to write some conditional logic to check for this

     

    eg

     

    Date = ADDCOLUMNS(
    CALENDAR(date(2017,1,1),date(2020,1,31))
    ,"YYYY|WW", 
    VAR _weeknum = WEEKNUM([Date],21)
    VAR _month = MONTH([Date])
    VAR _year = SWITCH(TRUE()
                ,_weeknum = 1 && _month = 12, YEAR([Date]) + 1
                ,_weeknum > 50 && _month = 1, YEAR([Date]) - 1
                , YEAR([Date])
    )
    return
    FORMAT(_year,"0000") & FORMAT( _weeknum,"00")
)

     

3 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    6 years ago

    I think this is because you are calling WEEKNUM with an illegal parameter of 21. According to the documentation it only supports a value of 1 or 2 (see https://docs.microsoft.com/en-us/dax/weeknum-function-dax)

     

    And the intellisense window only shows me those options too:

    I'm not sure why this does not throw a syntax error, but that's a separate issue. Using one of the documented types should fix your issue.

    • d_gosbell's avatar
      d_gosbell
      Super User
      6 years ago

      Actually I just found that dax.guide has the 21 option listed even though the official docs doesn't. If you are using true ISO8601 weeknum then the week at the end of the year will be "pulled" into the next year if it has 3 days or less. And the reverse happens too - if the first week in Jan has 3 days or less those days are considered as part of the prior year. You will probably need to write some conditional logic to check for this

       

      eg

       

      Date = ADDCOLUMNS(
    CALENDAR(date(2017,1,1),date(2020,1,31))
    ,"YYYY|WW", 
    VAR _weeknum = WEEKNUM([Date],21)
    VAR _month = MONTH([Date])
    VAR _year = SWITCH(TRUE()
                ,_weeknum = 1 && _month = 12, YEAR([Date]) + 1
                ,_weeknum > 50 && _month = 1, YEAR([Date]) - 1
                , YEAR([Date])
    )
    return
    FORMAT(_year,"0000") & FORMAT( _weeknum,"00")
)