cancel
Showing results for
Did you mean:

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

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:

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)

(ascending)

Thanks,
Ben
Helper I

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