Sorting Date & Month Columns in Descending Order in Power BI Matrix Visual

Introduction

Power BI’s Matrix visual is a powerful tool for analyzing and comparing data. However, many users face a common challenge—getting months and dates to display in the correct descending order. If you’ve ever seen January show up before December, you know how frustrating this can be!

In this blog, we’ll explore a step-by-step method to fix this issue and ensure your date columns always sort correctly.

Step 1: Create a Date Table

The first step is to set up a dedicated Date Table. This table is essential for sorting and filtering in Power BI. Make sure your Date table includes:

• Day

• Month Name

• Month Number

Having these columns gives you flexibility in creating sort logic later.

Step 2: Create a [Date Sort] Column

Next, create a column called Date Sort. This ensures each date aligns with the proper month-end value.

DAX:

         Date Sort = (ENDOFMONTH('Date'[Date]) - ('Date'[Day])) + 1

This works by:

• Taking the last day of the month (ENDOFMONTH)

• Subtracting the day number

• Adding +1 to realign the dates

  
  

   

  Step 3: Refine with [Month Sort] Column

  Even with Date Sort, sorting across years can still be tricky. That’s where the Month Sort column comes in.

  DAX:
  Month Sort = INT(
                                    YEAR('Date'[Date]) & 13 - MONTH('Date'[Date Sort])
                                )

   

  This approach:

  • Combines Year and Month values

  • Uses a small trick (13 - MONTH) to flip the order

  • Ensures months are sorted correctly across multiple years

   

   

  

  
  Step 4: Create a DateKey Column

  Finally, create a DateKey column to establish relationships with your fact tables.

  DAX:

            DateKey = INT(FORMAT([Date Sort], "DDMMYYYY"))
  
  

  This helps maintain accuracy when connecting your Date table with other tables.

  
  

  Conclusion

  Sorting dates in Power BI Matrix visuals doesn’t have to be a headache. By:

  1. Creating a Date Table

  2. Adding a Date Sort column

  3. Refining with a Month Sort column

  4. Building a DateKey for relationships

   

  With these steps, your Power BI Matrix will always present dates in the correct descending order, keeping your reports clean, accurate, and professional.