Forum Discussion

aralbrecht's avatar
aralbrecht
New Member
6 years ago

Sort by column error

Hello all,

 

I am attempting to create a line chart by week that spans multiple years. I would like Week 52 of 2019 to show up before Week 1 of 2020 on the X-axis.

 

In column A I have all possible dates for each year. In column B, I have a "Week 1, Week 2, Week 3, etc." assigned to a day, with each Week repeating 7 times. In column C, I have a field that is the year-month-day combo in the format YYYYMMDD.

 

When trying to sort column B (Week data) by column C, I receieve the error:

 

We can't sort the 'Week" column by 'YearMonthDay.' There can't be more than one value in "YearMonthDay' for the same value in 'Week'. Please choose a different column for sorting or update the data in 'YearMonthDay'.

 

I have read other forum posts, but I believe this issue is different. I have even looked at the data in an Excel PivotTable, and can confirm there is not more than 1 count of any YearMonthDay value.

 

Can anyone assist? Thanks in advance!

Need Help

2 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    This is the same issue. What is happening is column C is at the daily granularity level - YYYYMMDD, so 365/366 values per year. But your week is obviously only at the week level, so 52/53 values per year. Your column C needs to be the week number. You can get this easily in your date table in Power Query using Date.WeekOfYear([Date]), or in DAX using WEEKNUM() if your date table was generated in DAX.

     

    The Sort By column has to have the EXACT same number of distinct values as the column you are sorting. Not one more, not one less. Or it won't work. Has to be a 1:1 relationship.