Sorting date filter in DESC order in Power BI

Question

I have a date filter for my dashboard and depending on which end of month date the user selects the dashboard displays the appropriate values for the selected month and all of that works correctly. However I would like my date filter to be sorted in DESC order for the user so they do not have to scroll down to the bottom to get the most recent month. I have sorted the dataset in DESC order for the query tied to my dashboard, I have gone to the DATA tab within Power BI and sorted the dataset in DESC order but no matter what I try the filter will not sort in DESC order. Any help is much appreciated.

Answer 1

Please make sure your date field is in proper format and then try to sort. If it is already in date format, please give a bit more details so that we can check.

Best Regards, Shani

Answer 2

Unfortunately in page level filters there is no option to sort date to descending order. One option is to use a Date slicer and sort it descending. But if you cant have date slicer then a work around is there to implement this.

For this you need to create two calculated columns. One a duplicate column of your date field and another to sort this field.

SortCol = Table[EOM Date]*-1
EOMDateCopy=LEFT([EOM Date],LEN([EOM Date]))

Sort the EOMDateCopy column with SortCol and drag EOMDateCopy in your page level filter.

Best Regards,
Shani

Answer 3

My suggestion is to build your own Calendar table. In "Data" View, create a new Table by writing DAX similar like this:

Calendar = 
VAR _MinDate = MIN('YourFactTable'[Date])
VAR _MaxDate = MAX('YourFactTable'[Date])
VAR _BaseCalendar = CALENDAR(_MinDate,_MaxDate)
RETURN
    GENERATE(
        _BaseCalendar,
        VAR _BaseDate = [Date]
        VAR _Year = YEAR(_BaseDate)
        VAR _Month = MONTH(_BaseDate)
        VAR _Quarter = QUARTER(_BaseDate)
        VAR _Day = DAY(_BaseDate)
        RETURN
            ROW (
                "Calendar Year", _Year,
                "Calendar Month", _Month,
                "Calendar Year-Month", _Year&"-"&IF(LEN(_Month)=1,"0"&_Month,_Month),
                "Calendar Quarter", FORMAT(_Quarter,"\Q0"),
                "Date Order", 9999 - (_Year+_Month+_Day)
            )
    )

"Date Order", 9999 - (_Year+_Month+_Day) create a descending order for your date.

Next, build a relationship between your Calendar table and your Fact table by relating the "Date" column of your Calendar table and your date column (version_no in my case) of your Fact table. Make sure these two columns have exactly the same date format.

Finally, click on the column and set "Sort by column" as Date Order

You will see your date filter is sorted in desc.