How do I sort date columns by date rather than alphabetically when connecting to an SSAS tabular model?

I have an SSAS tabular model with a column that stores date values. The SQL column is of type date

. On the SSAS side, the datatype of the column is date

. However, when connecting to a model from Excel, the date appears as text and alphabetically sorted.

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

Column in SSAS Table Designer:

SSAS Column

Column after sorting "A to Z" in Excel:

Excel

+3


source to share


1 answer


I couldn't get Excel to recognize dates, but I was able to work around it. SSAS returns sorted data, and Excel uses this ordering unless changed to A to Z

or Z to A

(so dates are sorted correctly initially). Thus, to display dates in reverse order in Excel, we need to reverse them in SSAS. This can be achieved using the column property Sort By Column

.

enter image description here



You have three columns: a reference column that decreases as the date increases, a regular column, Date

and a computed column Date Reversed

that contains the same values ​​as Date

but has a reference column specified as Sort By Column

. On the Excel side, if someone adds a dimension Date

, it will sort in ascending order, and if they add a dimension Date Reversed

, it will sort in descending order.

SSAS ColumnsDefaultSorted

+1


source







All Articles