Reply
afk
Advocate II
Advocate II

Sorting week number from previous year to new year

Is there a way to sort week numbers from the previous year to the new year on a line chart? e.g. Week 52 (2017) to Week 1 (2018)

 

I currently have a column with the function 'WEEKNUM ([date])' to pull the week numbers, but obviously when I sort by week number, Week 1 of 2018 will appear first.

 

Week Number.JPG

 

Thanks

 

1 ACCEPTED SOLUTION
jthomson
Solution Sage
Solution Sage

You'll want to work around this by making a year-week column, which you then tell your existing column to sort by - there's a few examples of this if you search (year-month columns being the same principle and a more common question), but you basically get it so that Power BI recognises that 201752 is less than 201801

View solution in original post

5 REPLIES 5
anthony_lecoq
Regular Visitor

Hello,

I had this issue too. In Europ e the Standard weeks are iferent than in US.

For exemple in Europe the 2023-01-01 is in week 52 and the 2023-12-31 is also in week 52.

 

If you do a hierarchy year-week-date, the 2023-01-01 shows at the end of the year,

 

I come to find-out another work arround to keep the 2023-01-01 at the megining of the year.

 
EU Week No =
IF( WEEKNUM('Calendar'[Date],11) = 1
&& WEEKNUM('Calendar'[Date],21) > 10 , " " , BLANK()
)
& FORMAT(WEEKNUM('Calendar'[Date],21),"00")

 

It is my 1st post, let me know if it helpfull ?

jthomson
Solution Sage
Solution Sage

You'll want to work around this by making a year-week column, which you then tell your existing column to sort by - there's a few examples of this if you search (year-month columns being the same principle and a more common question), but you basically get it so that Power BI recognises that 201752 is less than 201801

If i have data ranging over several years, is seems that it doesn't solve the issue. If i have multiple week 1, it gets messy. Do you know an answer for this?

Thank you, this worked great!

 

However, it seems to have only worked on a column chart. When applied to a line chart, it's still sorting the numbers from 0-52. 

 

Any idea why this is?

avatar user
Anonymous
Not applicable

How did you do that?. Can you please explain

 

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)