Matrix order with Date hierarchy

[larx_mandel]

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.

[larx_mandel]

I tried with Shift key but it is not working. It is ordering separately.

My Power BI version is Version: 2.139.1678.0 64-bit (january 2025).

Thanks.

[Anonymous]

Hi @larx_mandel ,

Please download the latest version of Power BI Desktop and check if you can order multiple fields simultaneously.

Download Microsoft Power BI Desktop from Official Microsoft Download Center

Best Regards

[larx_mandel]

With new version I have the same behavior. Any other solution to this problem? Thanks.

[Anonymous · Accepted solution]

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

[Anonymous]

Click on the Year column, then after it is sorted by year, hold down the Shift key and click on the Month column