How to sort date columns by date instead of alphabetically when connecting to SSAS Tabular model?

Question

I've got an SSAS tabular model with a column that hold date values. The SQL column is of date type. On the SSAS side, the column data type is Date. However, when connecting to the model from Excel, the date comes across as text and sorts alphabetically.

How can I get Excel to sort the column by date?

Column in SSAS Tabular designer:

Column after sorting "A to Z" in Excel:

Answer 1

I couldn't get Excel to recognize the dates, but managed to get around it. SSAS returns sorted data, and Excel uses that ordering unless changed to A to Z or Z to A (which is why dates are sorted correctly initially). So, to display dates in reverse order in Excel, what we need to do is to reverse them in SSAS. This can be achieved by using the Sort By Column column property.

What you have is 3 columns: a reference column that decreases as dates increase, a regular Date column, and a Date Reversed calculated column that holds the same values as Date, but has the reference column set as the Sort By Column. On the Excel side, if someone adds the Date dimension, it will sort in ascending order, and if they add the Date Reversed dimension, it will sort in descending order.