Assistance Needed with Matrix Table Sorting

Hi Anonymous , Thank you for reaching out to the Microsoft Community Forum.

Keep your Sales Dynamic Measure Table as it is. It already generates the Month & Year values you need, including fixed KPIs like “Contr.” and “LY”. Add a new Sort Order calculated column that adjusts based on the selected month in your slicer.

Example:

Sort Order =

VAR SelectedDate = SELECTEDVALUE(Dim_Calendar[Date], MAX(Dim_Calendar[Date]))

VAR SelectedMonthYear = FORMAT(EOMONTH(SelectedDate, 0), "MMM-YY")

VAR Month4Prior = FORMAT(EOMONTH(SelectedDate, -4), "MMM-YY")

VAR Month3Prior = FORMAT(EOMONTH(SelectedDate, -3), "MMM-YY")

VAR Month2Prior = FORMAT(EOMONTH(SelectedDate, -2), "MMM-YY")

VAR Month1Prior = FORMAT(EOMONTH(SelectedDate, -1), "MMM-YY")

RETURN

SWITCH(

    TRUE(),

    'Sales Dynamic Measure Table'[Month & Year] = "Contr.", 1,

    'Sales Dynamic Measure Table'[Month & Year] = Month4Prior, 2,

    'Sales Dynamic Measure Table'[Month & Year] = Month3Prior, 3,

    'Sales Dynamic Measure Table'[Month & Year] = Month2Prior, 4,

    'Sales Dynamic Measure Table'[Month & Year] = Month1Prior, 5,

    'Sales Dynamic Measure Table'[Month & Year] = SelectedMonthYear, 6,

    'Sales Dynamic Measure Table'[Month & Year] = "LY", 7,

    'Sales Dynamic Measure Table'[Month & Year] = "YTD", 8,

    'Sales Dynamic Measure Table'[Month & Year] = "QTD", 9,

    'Sales Dynamic Measure Table'[Month & Year] = "Vs LM", 10,

    'Sales Dynamic Measure Table'[Month & Year] = "% Vs LM", 11,

    'Sales Dynamic Measure Table'[Month & Year] = "% Vs LY", 12,

    'Sales Dynamic Measure Table'[Month & Year] = "% Vs YTD", 13,

    999

)

Once that’s in place, go to the Data view, select the Month & Year column and set Sort by Column -> Sort Order. This ensures your matrix columns update automatically based on slicer selection, without circular references.

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.