Forum Discussion
Sort by the value that contains date
Hello everyone,
I wonder if anyone could please help me understand how to sort the data by "week of" so that it's in order of the date. This "week of" field is a column that concatenated string "week of " and date column. I sorted it by "week off" column in ascending. But it seems that week of 4/5/2021 is out of place. Does this mean that the sort on "week of" shouldn't work the way I need it and it just happen to sort everything in order except "week of 4/5/2021", or I should be able to sort in this way and something is wrong here. The column "week of" is created as quick measure. It's not from power query.
Thanks in advance.
Hi shiex1 ,
The first thing to make clear is that [week of] is a Text type field, and as HotChilli said, the sorting((alphabetically)) is correct. If you want that [week of] field to be sorted by the date in it, you might consider changing the format of the [week of] field to the format in Table2 in the figure below. This will give you the sorting results you expect...
If the field [week of] is a calculated column, you can update its formula as below:
week of = "Week of "&FORMAT('Table'[Date],"MM/DD/YYYY")Or you can select the field [week of] and navigate to Column tools ribbon to Sort by Column the field [Date]...
Best Regards
3 Replies
Hi shiex1 ,
The first thing to make clear is that [week of] is a Text type field, and as HotChilli said, the sorting((alphabetically)) is correct. If you want that [week of] field to be sorted by the date in it, you might consider changing the format of the [week of] field to the format in Table2 in the figure below. This will give you the sorting results you expect...
If the field [week of] is a calculated column, you can update its formula as below:
week of = "Week of "&FORMAT('Table'[Date],"MM/DD/YYYY")Or you can select the field [week of] and navigate to Column tools ribbon to Sort by Column the field [Date]...
Best Regards
Thanks for the details and appologize for the late update. But this solution worked for me just now. Thanks again!
- HotChilli
Community Champion
It has sorted correctly (alphabetically), it's just not what you want.
You should be able to use 'Sort by Column' feature, using the original date as the other column
