Week 53 on X Axis

[Anonymous]

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.

Week.Num = WEEKNUM(Datum[Datum],21)

[V-lianl-msft · Accepted solution]

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.

[V-lianl-msft · Accepted solution]

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 · Accepted solution]

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]

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

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.