Sorting on columns in a matrix

[rpinxt]

Pic will make it clear :

What I wanted is Year-week in a descending order.

At leat putting Year-Month above is will get me the descending part because YM is sorted by YM- :

Dim_Date =

ADDCOLUMNS(

    CALENDARAUTO(),

    "YM", FORMAT([Date], "YYYY-MM"),

    "YM-", FORMAT([Date], "YYYY-MM") * -1

)

But for the weeks it is not fully working (but now at least 2025 comes first because of YM)

multiply by -1 is not an option I think  because I use this to get the week numbers:

"Year - Week (ISO)", YEAR([Date]) &"-"& WEEKNUM([Date], 21),

Anybody an idea how I can get the year - weeks to me descending?

[rpinxt]

@danextian ah took only a tiny adjustment to make it all work as expected 😄

Now all is perfect. Thanks!

[rpinxt]

I think I understand a bit @danextian 

My original Dim_Date table was connect to a table on rundate then from that connection it makes:

Dim_Date =

ADDCOLUMNS(

    CALENDARAUTO(),

    "Year", YEAR([Date]),

    "Quarter", FORMAT([Date],"\QQ"),

    "Quarter Nr", QUARTER([Date]),

    "Month", FORMAT([Date], "mmmm" ),

    "Mth", FORMAT([Date], "mmm" ),

    "Month Number", MONTH([Date]),

    "Week Number", WEEKNUM([Date]),

    "Week Number Txt", "Week " & WEEKNUM([Date]),

    "Week Nr (ISO)", WEEKNUM([Date], 21),

    "Week Nr (ISO) Desc", WEEKNUM([Date], 21),

    "Year - Week (ISO)", YEAR([Date]) &"-"& WEEKNUM([Date], 21),

    "Year - Week (ISO)2", FORMAT([Date], "-YYYY-WW"),

    "ISO Week Desc", WEEKNUM([Date], 21) * -1,

    "Week Day", FORMAT([Date], "DDD"),

    "Week Day Nr", WEEKDAY([Date], 2),

    "YM", FORMAT([Date], "YYYY-MM"),

    "YM-", FORMAT([Date], "YYYY-MM") * -1,

    "Period", FORMAT([Date], "MM-YYYY")

)

Now for the matrix I made a new date table names Dim_Date_Matrix and also connected to the Rundate.

This looks like :

Dim_Date_Matrix =

VAR _base =

    ADDCOLUMNS (

        CALENDAR ( DATE ( 2021, 1, 1 ), TODAY () ),

        "YYYY-MM", FORMAT ( [Date], "YYYY-MM" ),

        "Year Week",

            YEAR ( [Date] ) & "-"

                & FORMAT ( WEEKNUM ( [Date] ), "00" )

    )

RETURN

    ADDCOLUMNS (

        _base,

        "Year-Month Sort", RANKX ( _base, [YYYY-MM],, DESC, DENSE ),

        "Year-Week Sort", RANKX ( _base, [Year Week],, DESC, DENSE )

    )

Still don't fully understand but the Today() probably makes for suddenly week 2 is showing up.

The max for rundate at this time is 5th of Jan.

So in the original Dim_Date that leeds to Week 1.

[danextian]

CALENDARAUTO relies on the existing dates in your model. I don't know what dates you have there.  So when you use TODAY() in CALENDAR(), you're seeing week 2 since we already are in the second week of January.  Instead of today, use MAX(data[rundate])

[rpinxt]

Thanks @danextian 

Looks to be sorting but 2 major things:

Its january so it should be 2025-01 and not 02??

And were is suddenly week 50 and 47?? (and I have week 53 now)

[danextian · Accepted solution]

Hi @rpinxt 

The matrix visual does not offer an option to sort columns in descending or ascending order directly. Columns are sorted either alphabetically or based on custom sorting in ascending order. To achieve your desired sorting, you will need to create a custom sort column. Please refer to the example below:

DatesTable = 
VAR _base =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2024, 1, 1 ), TODAY () ),
        "YYYY-MM", FORMAT ( [Date], "YYYY-MM" ),
        "Year Week",
            YEAR ( [Date] ) & "-"
                & FORMAT ( WEEKNUM ( [Date] ), "00" )
    )
RETURN
    ADDCOLUMNS (
        _base,
        "Year-Month Sort", RANKX ( _base, [YYYY-MM],, DESC, DENSE ),
        "Year-Week Sort", RANKX ( _base, [Year Week],, DESC, DENSE )
    )