Sorting Day Name/ Date - not by week number / month number

[Anonymous]

Apologies if this is obvious - i have a report where the date is shown DAYNAME / Date with the most recent date at the top:

The value shown here is a calculated text column in my date table. It using sort by - calendar date:

I am recreating this report (moving from direct SQL connection to Power BI shared dataset and have followed the exact same process but I am now getting the error: 

I can't see how i am getting this error when the exact same thing works perfectly in the old report, the only values i can sort by are day number of month/ day number of week but these do not show the values in the way that i need them to show - would appreciate any help.

[V-lianl-msft]

The values in CALENDAR_DATE are unique but the values in Date Text Full are not.

For example, if your date table spans 2019 and 2020, then you'll have Jun-30 in multiple rows with 190630 and 200630 in the sorting column. So Power BI doesn't know which index to use for sorting purposes.

[Anonymous · Accepted solution]

So I was able to 'fix' this by going into power query , changing the date table to look only at a few dates, closing and applying, sorting by the date and then undoing the power query changes. This works for me as the report is only ever looking at the last 7 days.

Would still like to keep this ticket open to see if anyone knows of a good way of fixing it rather than the method I used.