Hi community, I have matrix table with a Date in a form of hierarchy (Year, Month). In our configuration months are displayed as text.I create a measure that calculates previous year data: M_PreviousYearData =CALCULATE( SUM(Sales), SAMEPERIODLASTYEAR(_mydataset[Date].[Date])) My table looks like: YearMonthM_PreviousYearData2024January202024February25... 2024November502024December802023January152023February40.... I want to order by Year descending, and then by Month, but matrix only allow to order by Year or Month. If I tried to add a Period to my table, and order by it then my measure stop working, returning BLANK. How can I order to obtain a table like:YearMonthM_PreviousYearData2024December802024November50... 2023December602023November30... Thanks for your help.Regards.

Hi larx_mandel , I created a sample pbix file(see the attachment), please check if that is what you want. 1. Create a sort order table 2. Sort the field [Month] of sort order table by the column [Sort Order] 3. Create the relationship between the sort order table and data table base on the field [Month] 4. Create a table visaul just as shown in the below screenshot Sort one column by another column in Power BI - Power BI | Microsoft Learn Sort a Column with a Custom Order in Power BI - RADACAD Best Regards

larx_mandel

Frequent Visitor

1 year ago

Solved

Matrix order with Date hierarchy

Hi community,

I have matrix table with a Date in a form of hierarchy (Year, Month). In our configuration months are displayed as text.

I create a measure that calculates previous year data:

M_PreviousYearData =

CALCULATE(

SUM(Sales),

SAMEPERIODLASTYEAR(_mydataset[Date].[Date])

)

My table looks like:

Year	Month	M_PreviousYearData
2024	January	20
2024	February	25
...
2024	November	50
2024	December	80
2023	January	15
2023	February	40
....

I want to order by Year descending, and then by Month, but matrix only allow to order by Year or Month. If I tried to add a Period to my table, and order by it then my measure stop working, returning BLANK.

How can I order to obtain a table like:

Year	Month	M_PreviousYearData
2024	December	80
2024	November	50
...
2023	December	60
2023	November	30
...

Thanks for your help.

Regards.

Need Help

Anonymous
1 year ago
Hi larx_mandel ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a sort order table

2. Sort the field [Month] of sort order table by the column [Sort Order]

3. Create the relationship between the sort order table and data table base on the field [Month]

4. Create a table visaul just as shown in the below screenshot

Sort one column by another column in Power BI - Power BI | Microsoft Learn

Sort a Column with a Custom Order in Power BI - RADACAD

Best Regards

Forum Discussion

Matrix order with Date hierarchy

5 Replies

Related content

Matrix Visual Hierarchy Level Problem

Matrix component - order question

matrix rows order

order a matrix

Matrix Column Order reseting