Reversing Date Hierarchy

[ShivGC]

Hi, 

Does anyone know how to reverse date hierarchy?

I have added the date into a matrix visual in the 'Columns' section but the years are showing from 2021 upwards, however I want the most recent year to be dipslayed first?

[danextian · Accepted solution]

Hi @ShivGC 

Matrix can be sorted only by the measure or row categories. Column categories are either sorted alphabetically or by using a custom column sort. You can rank the year in your dates table to get the reverse sort.

Year Sort = 
RANKX(ALL(Dates[Year]), Dates[Year],,DESC,Dense)

As the rank calc column is referencing the year column, year cannot be sorted directly by this column as this will cause circular dependency.

Your alternative is to create another calculated column referencing the original year column, custom sort that instead and then use it in your viz.

Alternatively and if you are using  DAX calendar, you can the sort column directly in to the table itself and not as a calc column. Here's a sample DAX calendar with a year sort/rank column built-in

Dates = 
VAR __MIN =
   DATE ( 2022, 1, 1 )  
VAR __MAX =
    TODAY()
VAR __BASE =
    CALENDAR ( __MIN, __MAX )
VAR __RESULT =
    ADDCOLUMNS (
        ADDCOLUMNS (
            __BASE,
            "Year", YEAR ( [Date] ),
            "Month Long", FORMAT ( [Date], "mmmm" ),
            "Month Short", FORMAT ( [Date], "mmm" ),
            "Month Number", MONTH ( [Date] ),
            "Month and Year", FORMAT ( [Date], "mmm-yy" ),
            "YYYYMM", FORMAT ( [Date], "YYYYMM" ),
            "Quarter", "Q" & QUARTER ( [Date] ),
            "Day of Week Long", FORMAT ( [Date], "dddd" ),
            "Day of Week Short", FORMAT ( [Date], "ddd" ),
            "Day Sort", WEEKDAY ( [Date], 1 ),
            "Week of Year", WEEKNUM ( [Date] ),
            "Week Ending Saturday",
                VAR __DAY =
                    WEEKDAY ( [Date] ) - 7
                RETURN
                    [Date] - __DAY,
            "Start of Month", EOMONTH ( [Date], -1 ) + 1,
            "End of Month", EOMONTH ( [Date], 0 )
        ),
        "FY",
            IF ( [Month Number] >= 7, [Year] + 1, [Year] ),
        "Month FY", [Month Short],
        "Month Sort FY",
            IF ( [Month Number] > 6, [Month Number] - 6, [Month Number] + 6 ),
        "Week Num", WEEKNUM([Date]),
        "Day Number", DATEDIFF ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], DAY ) + 1,
        "Month and Day",  FORMAT([Date], "mm-dd"),
        "Week Ending Sunday",
            [Date]
                + MOD ( 1 - WEEKDAY ( [Date] ) + 7, 7 ),
        "Week Starting Monday", [Date] - WEEKDAY ( [Date], 3 ),
        "Year Rank", RANKX ( DISTINCT( __BASE ), YEAR ( [Date] ), , DESC, DENSE )
    )

RETURN
    __RESULT

[Uzi2019]

Hi @ShivGC 

Check all these post

https://community.fabric.microsoft.com/t5/Desktop/Date-in-Matrix-Column-how-to-sort-ascending/m-p/40...

https://community.fabric.microsoft.com/t5/Desktop/how-to-sort-a-matrix-in-powerbi-by-a-specific-colu...

https://community.fabric.microsoft.com/t5/Desktop/Sorting-on-columns-in-a-matrix/m-p/4357163#M136139...

https://community.fabric.microsoft.com/t5/Desktop/How-to-Sort-Columns-in-a-Matrix-Visual/m-p/4087770...

I hope you will find solution in above post.

I hope I answerd your question!