cancel
Showing results for 
Search instead for 
Did you mean: 
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
New Member

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?

Aha
New Member

How did you do that?. Can you please explain

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!