Forum Discussion
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 days).
Using calculated column below in my Date table.
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.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])
4 Replies
- amitchandak
Super User
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
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.
V-lianl-msftCommunity Support
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.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])
