Matrix Table - Month Descending Order

[CaseyM]

Hello,

I am real familure with DAX and Power Query in Excel, but fairly new to Power BI.  I have searched the forums and found many issues on the topic, but I still cannot get it to work.  

I am trying to create a matrix visual where the column headers are in descending order, but it is not working.  Here is what I did:

As you can see, the date is in the incorrect order.  Here is what I did to try to get it to work.

I created a calculated column showing the reporting month:

Here is the formula used:

Month for Reporting - Desc = 'Calendar'[Month for Reporting]

For the "Sort by" field I did the following in Power Query:

1.  I Grouped my "End of Month" field with a table.

2.  I sorted the "End of Month" Field in descending order:

3.  I added an index column from 0 in increments of 1 and named it "Desc Sort Order."

4. I expanded the table and loaded and closed.  Here is what it looks like.

On the Calendar table, I took the "Month for Reporting - Desc" field and applied the "Sort by column" to the "Desc Sort Order" field.

The data does not populate correctly.

When I use my other "Month for Reporting" field with sort applied to a different column, it works correctly in ascending order.

I feel like I am going crazy because I applied the same logic to the descending report, but it is not working.

Do you all have any other suggestions to get this sorted in the correct order?

Appreciate your response.

Casey

[v-chenwuz-msft]

Hi @CaseyM ,

If you want to change the order of the month. You can use  sort by column function to sort your month as you want. Only import a table with unique month and an index column. Then create a relationship between these tables.

For example, you want let apr'22 as the 1st one and jan'22 as the 2nd... refer [index2], then sort [month] by index2.

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CaseyM · Accepted solution]

Thank you both for responding.  I cleared all the confidential data and was going to add a sample pbix file, but then it worked!  I rebuilt the matrix visual without changing anything and it is working properly now.  Strange, but I am glad it is working now.

[MahyarTF]

Hi,

Not sure, what is in your Date Dim,
But you could add the Month number column in your Date Dim as below :

-------

"Month",MONTH([Date]),

-------

and then use the particular column in your Sort process.

Not sure I it is working for you, but I usually use the number as a sort field for Date records

[Ashish_Mathur]

Hi,

What do you mean by descending order of month?  Please be clear.  Also, share the download link of your PBI file.