Sort matrix/table by latest month column

[Oldnewbie]

Hi all,

I am trying to sort a table containing product production per month. I want the top row to have the product which was produced in largest amount in latest/current month. When I do traditional sort by column, I am getting rows sorted according to sum of values of all months. Is there any way to achieve desired output below?

Months are in one measure

Data:

Product	Oct	Nov	Dec	Jan
A	2	6	2	2
B	3	2	1	4
C	2	4	1	2

Desired output:

Product	Oct	Nov	Dec	Jan
B	3	2	1	4
A	2	6	2	2
C	2	4	1	1

[rupeshjiwtode]

My requirement to make dynamic...

 you have to use year = 2022 and month = 12 

Can you please elaborate this and suggest solution.

Thanks in advance

[negi007 · Accepted solution]

@Oldnewbie  in this case you can achieve it by creating a calcualted table like below. You can apply year and month filter based on the current date and make filter dynamic as well.

in this case i have order table which has transactional level data for each product and its category. from this table i have creted a summarize table like below

Table_Product_rank = SUMMARIZECOLUMNS(Orders[Product Category],FILTER('Calendar',YEAR('Calendar'[Date].[Year]=2022)),FILTER('Calendar','Calendar'[Month#]="12"),"Sales",SUM(Orders[Sales]))

then added rank calc column in this table

Rank = RANK.EQ(Table_Product_rank[Sales],Table_Product_rank[Sales],DESC)

this table will alwasy be sorted based on the latest month volume like below

you can sort the above table on the rank category

then you can link this table to your main order table,

then in your matrix visual you can use the category from the newly created table and data will be sorted based on the data in the new table.

have attached the pbix file as well.