Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.