Forum Discussion
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)
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?
Thanks
4 Replies
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.
You are welcome. So that you can use DAX's Date and Time Intelligence functions such as PREVIOUSYEAR, SAMEPERIODLASTYEAR etc.
