Report Slicer sorted by current year and respective quarters both in descending order

[PBIFanHook7]

I would like to sort a Report Slicer by Current Year descending order with 'Most Recent Year' text and its quarters in descending order listed first, then 2024 and its quarters in descending order listed second, then 2023 and its quarters listed third in descending order. I have the Current Quarter sort order working fine in descending order. It is the Year sort order I need help with.

Year/Quarter Report Slicer with Current Year and Current Quarters. The slicer's drop-down values are below:

  2023

    Q4 2024

    Q3 2024

    Q2 2024

    Q1 2024

  2024

    Q4 2024

    Q3 2024

    Q2 2024

    Q1 2024

  Most Recent Year

    Completed Quarter

    Q3 2025

    Q2 2025

    Q1 2025

DAX: 

Current Year = IF(DATE_MONTH[YEAR_NBR] = MAX(ETL_Report_View[Year]),"Most Recent Year",DATE_MONTH[Year_Text])

Current Quarter = IF(DATE_MONTH[QTR_Sort] = MAX(ETL_Report_View[QTR_Sort]),"Completed Quarter",DATE_MONTH[QTR_NAME])

I am using a QTR_Sort to sort the Current Quarter. Again this appears to be working.

QTR_Sort = DATE_MONTH[YEAR_NBR]*100 + DATE_MONTH[QTR_NBR]

I select Sort descending and Current Quarter in the actual Slicer's GUI's (3 dots) and More Options. Note that this method does not allow me to sort on multiple columns like Current Year and Current Quarter.

Therefore, I select the Current Year column, and use the Sort By Column Button under Column Tools. I tried using the same QTR_Sort to sort the Current Year, but receiving the following error:

'We can't sort the 'Current Year' column by 'QTR_Sort'. There can't be more than one value in 'QTR_Sort' for the same value in 'Current Year'. Please choose a different column jfor soring or update the data in 'SFY_QTR_Sort'.

I have tried using a Year_Text (text data type) defined below to sort the Current Year.

Year_Text = DATE_MONTH[YEAR_NBR]

I have tried using the Year_Number (whole number data type).

The Year_Text may be working and just sorting the year in order listed above (2023, 2024, Most Recent Year). It is not changing the Current Year order, it may be sorting it in ascending order, not sure what power bi sorting mechanism is when a column contains both numbers and text? Does it consider the data type? Current Year and Current Quarter are defined with data type of text.

Again, I want it to sort by Current Year descending order with 'Most Recent Year' text and its quarters in descending order listed first, then 2024 and its quarters in descending order listed second, then 2023 and its quarters listed third in descending order.

Thanks in advance, hopefully I have provided enough information. 

[MFelix · Accepted solution]

Hi @PBIFanHook7 ,

You need to sort the current year column by the year column on your table, regarding the quarters I would add a reverse sorting column so you would have the values starting from the oldest to the youngest.

Usually using a relative column for years and year/quarters you can easily do this.

You will have something similar to this:

[v-venuppu · Accepted solution]

Hi @PBIFanHook7 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @MFelix for the prompt response.

I have created a sample PBIX file using sample data to replicate the scenario.Please go through the attached PBIX file for your reference.

Thank you.

[v-venuppu]

Hi @PBIFanHook7 ,

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[PBIFanHook7]

v-venuppu, I accepted yours and MFelix responses as the solution. I attempted to accepted both has solutions, but it appears I may have mistakenly created 3 accepted solutions. Sorry, if I messed that up. Please cleaned it up, if you can on your end. Thanks for your help and feedback.  

[v-venuppu · Accepted solution]

Hi @PBIFanHook7 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @MFelix for the prompt response.

I have created a sample PBIX file using sample data to replicate the scenario.Please go through the attached PBIX file for your reference.

Thank you.

[PBIFanHook7]

Thanks v-venuppu! The information MFelix provided and the sample pbi file you provided is excellent. I am going to mark both of your feedback as the solution. 

[MFelix · Accepted solution]

Hi @PBIFanHook7 ,

You need to sort the current year column by the year column on your table, regarding the quarters I would add a reverse sorting column so you would have the values starting from the oldest to the youngest.

Usually using a relative column for years and year/quarters you can easily do this.

You will have something similar to this:

[PBIFanHook7 · Accepted solution]

Thanks MFelix! The information you and the sample pbi file v-venuppu provided is excellent. I am going to mark both of your feedback as the solution.