Assistance Needed with Matrix Table Sorting

[DVannurVali]

Hi All, 

I hope you're doing well.

I need some help with sorting columns in a matrix visual. I'm currently using the following measure to display values:

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

RETURN
SWITCH(
TRUE(),
SELECTEDVALUE('Sales Dynamic Measure Table'[Month & Year]) = "Contr.", FORMAT([% Channel Sales Contribution], "0.00%"),
SELECTEDVALUE('Sales Dynamic Measure Table'[Month & Year]) = FORMAT(EOMONTH(SelectedDate, 0), "MMM-YY"), [CM Sales],
SELECTEDVALUE('Sales Dynamic Measure Table'[Month & Year]) = FORMAT(EOMONTH(SelectedDate, -1), "MMM-YY"), [LM Sales],
SELECTEDVALUE('Sales Dynamic Measure Table'[Month & Year]) = FORMAT(EOMONTH(SelectedDate, -2), "MMM-YY"), [Last 2M Sales],
SELECTEDVALUE('Sales Dynamic Measure Table'[Month & Year]) = FORMAT(EOMONTH(SelectedDate, -3), "MMM-YY"), [Last 3M Sales],
BLANK()
)
----------------------------------------------------------------------------------------------------------------------------------

In the Year & Month slicer, I’ve selected Apr-25, and I want the matrix columns to appear in the following order:

Contr. | Jan-25 | Feb-25 | Mar-25 | Apr-25

However, currently the sorting is happening alphabetically, which is not what I want.

Could someone please guide me on how to apply a custom or dynamic sort to achieve the desired order?

Thanks in advance!

BR,
Vannur Vali

[v-hashadapu]

Hi @DVannurVali ,
I hope the information shared was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you!

[v-hashadapu]

Hi @DVannurVali ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!

[DVannurVali]

Hi @rajendraongole1, 

Thank you for the quick response.

I’ve created a new calculated table called "Month & Year"

Sales Dynamic Measure Table = UNION(DISTINCT(Dim_Calendar[Month & Year]) , ROW("Month & Year", "Contr."), ROW("Month & Year", "LY"), ROW("Month & Year", "YTD"), ROW("Month & Year", "QTD"), ROW("Month & Year", "Vs LM"), ROW("Month & Year", "% Vs LM"), ROW("Month & Year", "% Vs LY"), ROW("Month & Year", "% Vs YTD"))

I followed your suggestion and created a calculated column as you described, but I'm encountering the following error:

Could you please help me resolve this? Thanks in advance!

BR,
Vali

[v-hashadapu]

Hi @DVannurVali , 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.

[rajendraongole1]

Hi @DVannurVali  - You’ll need to add a new column in Sales Dynamic Measure Table that assigns a numeric sort order for each Month & Year value.

Sort Order =
SWITCH(
TRUE(),
'Sales Dynamic Measure Table'[Month & Year] = "Contr.", 1,
'Sales Dynamic Measure Table'[Month & Year] = "Jan-25", 2,
'Sales Dynamic Measure Table'[Month & Year] = "Feb-25", 3,
'Sales Dynamic Measure Table'[Month & Year] = "Mar-25", 4,
'Sales Dynamic Measure Table'[Month & Year] = "Apr-25", 5,
999 -- fallback for unknown labels
)

Now, select the Month & Year column in the Power BI Data view, and click:

Modeling tab → Sort by Column → Sort Order

Power BI will now use the Sort Order column to sort the Month & Year column, preserving the order you defined, instead of sorting alphabetically.

This helps. please check.