How to sort a date-type slicer in descending order

[agusti_oliveres]

Hi,

I have a normal slicer with a date-type field, with month granularity. By default, the months are sorted by ascending order (from the older months at the top to the current ones at the bottom), but I would like to sort it in descending order. I have not been able to figure out how to do it. Can anyone help me with that?

[Anonymous · Accepted solution]

Is it simply the months you want in reverse order?  For example you are showing a slicer with December, November etc?

If thats the case, you will need a date table and a sort column.

If you have a date table already, GREAT!  If not you will need to make one, which is fairly easy.

Once you have that, you should have a column with the Month Name and the Month Number.  This Month Name will be the column for your slicer.

Next you need a calculated column called "Month Sort" and its formula will be to take the month number and make 12 = 1, 11 = 2 etc.  We can do that with math by doing:

Month Sort = 13 - [Month Number]

Lastly, select your Month Name column and in the ribbon under "Modelling" select 'Sort By Column' and choose your Month Sort.

[nagasaikumarb22]

Hi i got your logic see now i got here a question lets suppose there are two years 2022/12/31 and 2023/2/25 now will this logic wont help.can you provide me an alternate solution

[mi_a]

I tried to do this with date using a calculated column like this.

SortOrderDateDesc = - DATEDIFF('DateDim'[date_natural].[Date], date(9999, 12, 31), DAY)

But when I try to sort the slicer on the date_natural column, I get error:

"Sort by another column. This column can't be sorted that is already sorted, directly or indirectly, by this column".

Any suggestions?

[mi_a]

I tried to do this with date using a calculated column like this.

SortOrderDateDesc = - DATEDIFF('DateDim'[date_natural].[Date], date(9999, 12, 31), DAY) 

But when I try to sort the slicer on the date_natural column, I get error:

"Sort by another column. This column can't be sorted that is already sorted, directly or indirectly, by this column".

Any suggestions?

[mi_a]

I tried to do this with date using a calculated column like this.

SortOrderDateDesc = - DATEDIFF('DateDim'[date_natural].[Date], date(9999, 12, 31), DAY) 

But when I try to sort the slicer on the date_natural column, I get error:

"Sort by another column. This column can't be sorted that is already sorted, directly or indirectly, by this column".

Any suggestions?