Sort by the value that contains date

[shiex1]

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.

[Anonymous · Accepted solution]

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

[shiex1]

Thanks for the details and appologize for the late update. But this solution worked for me just now. Thanks again!

[HotChilli]

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