cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srlabhe123 Member
Member

Sort Date Column in Matrix

Hi All,

I have a scenario where I am using a date field as Column in matrix visusal. But when I am trying sorting on the same field its not working the way it shoud be. The older date reflecting as first column and newer date as last. I wanted to have exactly in reverse order

Capture_CC_Metrics.JPG

 

Below are the options I have alreday tried

1. Sorted on base of index of date,i.e. I alreday have a column which is index for date nothing but sequence of date in year.01/01 would have 1 and 02/01 would have 32 and so on.

2. In data model tried selecting date and sort by on index column

 

But its not working the way it should.

 

Help is appreciated here.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Sort Date Column in Matrix

@srlabhe123,

What fields of the above table do you use to create Matrix visual? You can rank the date in Power BI Desktop query Editor following the guide in this similar blog.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Moderator v-yuezhe-msft
Moderator

Re: Sort Date Column in Matrix

@srlabhe123,

In your scenario, please add the index in reverse order, in other words,  02/01 would have 1, 01/01 would have 32, then sort the date by index column. There is an example for your reference.

1.PNG2.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
srlabhe123 Member
Member

Re: Sort Date Column in Matrix

Thanks, what wold be the formula for index here?

Moderator v-yuezhe-msft
Moderator

Re: Sort Date Column in Matrix

@srlabhe123,

Could you please share sample data of your original table so that I can provide you appropriate DAX?

Regards,
lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
srlabhe123 Member
Member

Re: Sort Date Column in Matrix

Please find sample data below:

 

COUNT_DATETaskCrenTimeTaskComplStampDIFFWEEK_END_DATECALENDAR_DATEMONTHSYSDATEMONTH_NAMELAST_7_DAYSDAY_NUMBER_OF_YEARZONELOCATIONLOCN_IDSTAT_CODETASK_IDEXCEPTIONABC_INDICATORExpected QuantityCounted QuantityQty VarainceLatestComplnTimeEarliestCreanTimeTasksGeneratedTasksCompleteTaskExpired% Complete% ExpiredAccuracyAccuracyBinary
01/21/201901/21/201901/21/2019380.9227-JAN-1921-JAN-19122-JAN-19JANUARYY21A2A2-0925-02E00323179013272766PendingD3131021-JAN-19 11.03.14.547000000 PM21-JAN-19 04.42.19.363000000 PM1101011
01/22/201901/22/201901/22/2019027-JAN-1922-JAN-19122-JAN-19JANUARYN22C2C2-0317-02F00201609013339038PendingD4444022-JAN-19 03.53.33.083000000 PM22-JAN-19 03.53.33.083000000 PM1101011
01/22/201901/22/201901/22/2019027-JAN-1922-JAN-19122-JAN-19JANUARYN22A3A3-0927-01E00308789013339447PendingD2532421122-JAN-19 04.20.16.643000000 PM22-JAN-19 04.20.16.643000000 PM110101.050
01/21/201901/21/201901/21/2019341.2227-JAN-1921-JAN-19122-JAN-19JANUARYY21A1A1-0416-03F00328409013272734PendingD5554121-JAN-19 10.16.42.353000000 PM21-JAN-19 04.35.29.230000000 PM110101.020
Moderator v-yuezhe-msft
Moderator

Re: Sort Date Column in Matrix

@srlabhe123,

What fields of the above table do you use to create Matrix visual? You can rank the date in Power BI Desktop query Editor following the guide in this similar blog.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

srlabhe123 Member
Member

Re: Sort Date Column in Matrix

Created matrix by Location as row, Calendar Date as Column and %complete as values in matrix

Highlighted
srlabhe123 Member
Member

Re: Sort Date Column in Matrix

Thanks the blog helped me, it worked like charm... 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 152 members 1,689 guests
Please welcome our newest community members: