Table visual: Sort date DAX column in descending order

[andrelee]

Dear Community, 

The reporting dashboard I am working on has a table visual with the following columns: Date, Pageviews, Sessions, and Users. Please take a look at the screenshot below (the values are blurred out).

The first column, Date, is a "time-based grouping" DAX measure. The measure groups the table data by a date range based on the selected slicer option: Date (day), Week, Month, Quarter, or Year.

By default, when a grouping option is selected, the data in the visual is sorted by Date in ascending order or not sorted at all.

I am trying to figure out how to sort the grouped output of the selected date range in descending order and permanently save the change.

Please let me know if you have any suggestions for achieving this.

Thank you

[andrelee]

@DataInsights 

Thank you for the quick reply! I've created the reversed column but used a different syntax. The idea is the same, though. 

reverse_sort = VALUE(DATEDIFF(DATE(1990,1,1),[the_date],DAY))*-1

Next, I followed the instructions in the article to set the column to use for sorting - the reverse_sort column needs to be used for sorting the_date column. However, an error message pops up when I try to assign the sorting column. The error says, "A circular dependency was detected..."

Please advise

[DataInsights · Accepted solution]

@andrelee,

You'll need a separate date column to avoid the circular dependency. You could create a reverse_date column that is a clone of the date column. Then, sort reverse_date by reverse_sort.

[DataInsights]

@andrelee,

Try creating a reverse sort column. Here's an example for a DAX date table:

CONVERT ( FORMAT ( [Date], "YYYYMM" ), INTEGER ) * -1

Sort the displayed date column (e.g., 2024-03) by the integer column above.

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column