cancel
Showing results for
Did you mean:

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.

Thanks

1 ACCEPTED SOLUTION
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

5 REPLIES 5
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 ?

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

New Member

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?

New Member

How did you do that?. Can you please explain

Announcements

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 Design Challenge

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

Check it out!

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

Top Solution Authors
Top Kudoed Authors