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])

     

V-lianl-msft's avatar
V-lianl-msft
Icon for Community Support rankCommunity Support
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
Not applicable
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])