Sort by column grouped by year ascending

[Anonymous]

Hello,

I'm trying to make a report where the columns are week values calculated from a sql server datetime.  The week values are calculated using the DAX calculation FORMAT(WEEKNUM([timestamp],2),"General Number"), and in edit queries, the datetime column is sorted from oldest to most current.

This has worked for the entries in 2018, but the column for week 10 from 2019 have wrapped around to the left, whereas week 9 of 2019 is in the correct sort order. And the columns are now in the order:

10, 44, 45, 46, 47, 48, 9

instead of:

44, 45, 46, 47, 48, 9, 10

Clicking the ... -> sort by in the table only allows me to sort by rows or by row values.

Any help would be appreciated,

Thanks.

[edhans · Accepted solution]

You need to create a helper column in your table that would be the combination of year and week number, then tell your data to sort by that column. So it wouldn't be 9 & 10, but 201909 and 201910, which would then sort after 201848, 201849, etc.

Use the Sort By column feature. That is what my MMM-YY Sort field is doing for me.