cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Matrix sort by another column

Hi,

I have a matrix - project hours by month.

I wish to sort the columns by fiscal year period and display the fiscal period year text.

I have sorted my table by the fiscal year period (key)

Image 45.jpgImage 44.jpg

 

My matrix is still showing the incorrect sort order when I put the fiscal period year text in the columns. I would like it to sort chronologically. Can someone please help?

 

Image 46.jpg

 

Thanks

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Sorry, I should edit, I clicked on the "Fiscper Text" Column, and chose the wrong sort by column.

I have resolve my own issue

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

 

You should ideally build a Calendar Table with Months and Year extracted using the following calculated column formulas

 

Year=Year(Calendar[Date])

Month=FORMAT(Calendar[Date],"mmmm")

 

There should be a relatiosnhip from the Date column of your base data Table to the Date column of your Calendar Table.  Create another 2 column Table (Month_order Table) with Month and Order.  Create a relatiosnhip from the Month column of the Calendar Table to the Month column of the Month_order Table.  In the Calendar Table, use the RELATED() function to bring over the Order column from the Month_order Table (remane this new column to Order).  Click on any month in the Calendar Table and go to Home > Sort By > Order.

 

In your visual, drag the Year and Month from the Calendar Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thank you for the advice.

 

Why is it ideal or better to build a calendar table?

You are welcome.  So that you can use DAX's Date and Time Intelligence functions such as PREVIOUSYEAR, SAMEPERIODLASTYEAR etc.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Sorry, I should edit, I clicked on the "Fiscper Text" Column, and chose the wrong sort by column.

I have resolve my own issue

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.