Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Week 53 on X Axis

  Hello,   I think the image explains it's self. How can I sort, so week 53 jan 2021(3 days) will move all the way to the left as first week of 2021. Week 53 dec 2020 is should still remain (4 da...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi Anonymous ,

     

    You can't sort based on your original data because 53 weeks are in different years. You need to concatenate with the year field to sort.

    Please create 2 new columns:

    year week = YEAR('Table'[Date])*100+'Table'[week]

sort = YEAR('Table'[Date])*100+WEEKNUM('Table'[Date],2)

    Then sort year week column by sort column.

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    Thanks a lot. This is what I already tried.

    Unfortunatly this will show the year values twice. If I remove year from the visual sorting will again will be incorrect. In that case I need another 2 columns that will correct the months. But, then I would have the same problem (week 53 2020 & jan 2020).  So I guess what I'm looking for is not possible. 

     

    Nevertheless I will accept this as solution and I made my own format. As shown in the picture.

    year week = "Wk " & Date[Week.Num] & " " & Date[Month.Name.Short] & " " & YEAR(Date[Date])

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , You have to create a sort column like

 

Sort Week  = if[Week.Num] =53  && month(Datum[Datum) =1, 0, [Week.Num])

 

Week.Num 1 =[Week.Num]

 

Now Week.Num 1 on Sort Week   and use that is visual

 

Refer, why this way : https://www.youtube.com/watch?v=KK1zu4MBb-c

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks a lot for your reply! Unfortunatly I think I missed something.

    I created both the [Sort Week] and [Week.Num 1] columns. I (obviously) can't still sort any columns because of multiple values.

     

    So if I use [Week.Num 1] in my visual I have the same result as before.

    If I use [Sort Week] in my visuals it displays week 0 instead of 53.