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
Jaxidian
Frequent Visitor

Sort a Date Hierarchy in Chronological/Reverse Chronological Order in a Matrix

I have a Matrix that's essentially a Date Hierarchy & aggregated Financials. How do I make it such that this can be sorted both in Chronological order and Reverse Chronological Order? By default, Chronological order works but I cannot then get it to reverse. Everything I try seems to only sort a single level descendingly while the subgroupings flip back to be ascending. If I have a table with just any one of these fields in it, they sort correctly in both ascending and descending orders (i.e. the Sort By columns are properly set). The problem is specifically when say to sort the Matrix in a Descending way by Day, then the subgroup headers for Month, Quarter, and Year are Ascending but the Days within each Month are descending.

 

I specifically want a reverse-chronological order when "Descending" is selected and chronological order when "Ascending" is selected. I don't want to hack all of the levels to *always* be reverse-chronological order, I want the consumer to be able to switch back and forth between the two.

 

Fully-expanded hiearchy: Year -> Quarter -> Month -> Day

 

 

Desired (when Descending is selected):

2018: $11602
    2018 Q4: $1234
    2018 Q3: $2345
    2018 Q2: $3456
    2018 Q1: $4567
2017: $11602
    2017 Q4: $1234
    2017 Q3: $2345
    2017 Q2: $3456
    2017 Q1: $4567

 

What I get if I sort by Quarter (descending):

2017: $11602
    2017 Q4: $1234
    2017 Q3: $2345
    2017 Q2: $3456
    2017 Q1: $4567
2018: $11602
    2018 Q4: $1234
    2018 Q3: $2345
    2018 Q2: $3456
    2018 Q1: $4567

What I get if I sort by Year (descending):

2018: $11602
    2018 Q1: $1234
    2018 Q2: $2345
    2018 Q3: $3456
    2018 Q4: $4567
2017: $11602
    2017 Q1: $1234
    2017 Q2: $2345
    2017 Q3: $3456
    2017 Q4: $4567

 

2 REPLIES 2
Anonymous
Not applicable

Hi,

 

I'm not sure this is possible by default now to sort by two columns at once. There is the following idea if you want to vote for it:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/11185431-add-a-multiple-columns-s...

 

I think you could create an additional column like this one:

YQ = (Sheet1[Year] ^2) + RIGHT(Sheet1[Quarter],1)
 
The issue here is that you'd have to include this column in your visual and sort by this column. It produces the following results:
 
(descending)
descending.PNG
 
(ascending)
ascending.PNG
Please let me know if this is helpful or if I've misunderstood your issue.
 
Thanks,
Ben

What if I need to do this by Month?  where my Month column is the name of the month.

YM = ([Date to PO Complete].[Year]^2)+RIGHT([Date to PO Complete].[Month],1)
Your dax wants to change it to a number

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.