Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

WeekNum sequence issue

Hi there,   I created a Date table as following: The weekNumber dax is: WeekNum = "Wk" & WEEKNUM('Date'[Date])     However, the weekNumber does not work well in chart sequence. The ac...
Need Help
Show and Tell
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    Hi Anonymous ,

     

    you need two columns, one with the week as an int, the other as the week as a string, then you will sort the week as a string by the week as int. see image below

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi richbenmintz,

 

I've set it. but as you can see, "wk10" is the one after "wk1", but not "wk2" after "wk1"

 

Did I set it correctly?

Thanks.

H

richbenmintz's avatar
richbenmintz
Icon for Resident Rockstar rankResident Rockstar
5 years ago

Hi Anonymous ,

 

That is the correct setting, but you need to sort by the weenknum as a number, the string wk3 comes after wk29

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thats right richbenmintz, so my question is, is there a way to make the weekNum in the correct sequence while keeping "wk" prefix. So that in the chart I can see axis like: 2020 wk1, 2020 wk2 instead of 2020 1, 2020 2

    • richbenmintz's avatar
      richbenmintz
      Icon for Resident Rockstar rankResident Rockstar
      5 years ago

      Hi Anonymous ,

      create column that is the week as an int

      WEEKNUM('date'[Date])

      then set the sort by column to the weekNum as int column

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi  richbenmintz ,

         

        When I create a column and set to int, it is fine.

         

        But once "wk" prefix is added, the sequence is wrong again.

        So my question is, is there a way to keep the prefix "wk" while keeping the correct sequence, 1, 2, 3?

         

        I want to display in visual as: 2020 wk1, 2020 wk2, but not 2020 1, 2020 2.

         

        Please let me know if I was in wrong setting. New to Power BI.

        Thank you.

        H