Can't sort weeks of year properly in column chart

[milesh]

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

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: 

This gets the error in Power BI:

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?

[LoreGaggio]

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]

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).

[milesh · 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.

[Anonymous]

Hi @milesh, 

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

[haizman]

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.

[Berl21]

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

[haizman]

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