Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
milesh
New Member

Can't sort weeks of year properly in column chart

I have a column chart comparing revenue from Week 1 of 2016 to Week 1 of 2017 to Week 1 of 2018, like this:

 

ss.png

 

I need "Week 10" to show after "Week 9". These weeks are lined up day-to-day so they'll be the equivalent Monday to Sunday on each year, eg. the week of Monday 1st January 2018 to Sunday 7th January 2018 will be matched to Monday 2nd January 2017 to Sunday 8th January 2017 (done using a separate VLOOKUP column in Excel). Therefore in Power BI if I were to create a seperate column like the following and tell it to sort by that:

 

Week Number = WEEKNUM(Dashboard[Date])

 

It doesn't work as the weeks are different, they're not lined up day-to-day.

 

I've tried creating another column WeekSort in the Excel data files, which just displays the order the Weeks should be sorted in: 

 

excel.png

 

This gets the error in Power BI:

 

errorbi.png

What should be in the WeekSort column in the Excel files then? There are of course only 52 entries I would ever need in that column to show it the order to sort by (Week 1, Week 2 etc) but thousands of rows of data in my Excel data files, does there need to be a value in WeekSort for each value in the Week column? How should this be done?

1 ACCEPTED SOLUTION

In both these I'm still getting the error "There can't be more than one value in 'WeekNumber' (or 'WeekSort') for the same value in 'Week'.

 

In Excel I'll try making a column with forumulas like =RIGHT(A1,LEN(A1)-5) that'll for example change "Week 12" to "12" for every row in the data excel files and see if that works.

View solution in original post

7 REPLIES 7
LoreGaggio
Advocate I
Advocate I

My easiest way of solving is usually to create a dummy column that is a concatenation of YYYY & Week Nb.

Then i turn this column into a Number format.

And then I use the "Sort by column" feature under "Column tools", using this dummy column to sort the original week number column.

This method avoids the typical error saying "There can't be more than one value A for each value B". Since this error is usually happening when you try to sort the column Week number by a column containing the respective nb of that week --> that actually repeats every year, making impossible to use this sorting on multi-year datasets. While using the concatenation of year and week nb you create a unique ID (e.g. for week 1 or 35, create something like 202401 or 202435, which are different from 202301 or 202335)

Anonymous
Not applicable

For the Weeksort, use number for ordering and make sure the column type of Weeksort is Number in Power B. Select the Week column, click Modeling > Sort by Column and then choose sort by Weeksort. After that, you should get the order to sort by (Week 1, Week 2 etc).

2018-03-19_9-04-16.png

 

 

 

In both these I'm still getting the error "There can't be more than one value in 'WeekNumber' (or 'WeekSort') for the same value in 'Week'.

 

In Excel I'll try making a column with forumulas like =RIGHT(A1,LEN(A1)-5) that'll for example change "Week 12" to "12" for every row in the data excel files and see if that works.

Anonymous
Not applicable

Hi @milesh

Did you solve the issue? I'm getting the same error message when I want to sort my weeks by date. 

haizman
Advocate I
Advocate I

I would suggest creating another column that is called WeekNumber. Make sure it is a whole number field and have it just be 1 through 52. Then sort by that using sort my another column.

@haizman I know it's a very old post, but I solved my issue thanks to your input! Just duplicated my ISO Weeknumber column and then sorted it by it. 
Not sure why it wouldn't work with any other attempt I did, but finding your answer did the trick. Thanks a lot!

You're welcome. Glad it helped. Sometimes you find something (even older) that works and it makes all the difference! Have a good one!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.