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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
santoshlearner2
Resolver I
Resolver I

Date in Matrix Column how to sort ascending

Dear All,

 

I have a table in which there are calculated columns which does Plus minus of daily stock, the issue is when i put the dates in the matrix it sorts by ascending order, i tried creating duplicate calendar table then linking it with the main date table,  even tried rank, index but it does not work, can somone assist

 

Warm Regards

3 ACCEPTED SOLUTIONS
Ritaf1983
Super User
Super User

Hi @santoshlearner2 
For sorting the dates in descending order you need to have the column with its numeric value multiplied by (-1).
To attach it from the date table that was made with pq you can
1. duplicate the date column

Ritaf1983_0-1722082910901.png

2. change the data type to whole number:

Ritaf1983_1-1722082968418.png

3. multiply it by (-1)

Ritaf1983_2-1722083022381.pngRitaf1983_3-1722083050193.png

Close and apply, and modify the date sort by this new column from the table view :

Ritaf1983_4-1722083150655.png

Result :

Ritaf1983_5-1722083183691.png

Unfortunately, I couldn't find a solution for the scenario where you're using a calendar created with DAX. However, that type of calendar is less flexible. I recommend using a PQ calendar instead. I've attached a sample file for your reference. You can find more information about PQ calendars, including a script for creation, in the link.

https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

Try to download my file and follow the steps / share link to your pbix and I will check...

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

You need to multiply it with (-1) not 1...
The column for sorting should have negative numbers...

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

8 REPLIES 8
santoshlearner2
Resolver I
Resolver I

hi sorry type error i multiplied it by - 1, i am accepting your reply as a solution, i appreciate your efforts to answer.  I am doing something wrong.

 

Thank you

santoshlearner2
Resolver I
Resolver I

Hi Rita,

 

I have data table, which has dates, in that table there are calculated columns, which gives the result, about daily changes, Now when i put this in matrix the last date comes on the right, which i wanted to put at the begining.  I thank you for your efforts the steps which i did.

1) From the base table i duplicated the date table, converted into whole number, multiplied the number by 1, then i went to the table view and sorted in the order, but the date in the matrix does not move an inch from its place, it keeps hanging on the right side.  Nevertless thank you so much effort you took for this. Unfortunately i cannot paste or give access to the file as it is restricted.

You need to multiply it with (-1) not 1...
The column for sorting should have negative numbers...

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
santoshlearner2
Resolver I
Resolver I

Hello Rita,

 

I appreciate the effort you too to assist, hats off. though i am doing something wrong which i cannot figure it out.

 

Thank you

Try to download my file and follow the steps / share link to your pbix and I will check...

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Hi,

 

I got it, Problem solved.

Amazing

 

Warm Regards

 

Santosh

Happy to help 🙂

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
Ritaf1983
Super User
Super User

Hi @santoshlearner2 
For sorting the dates in descending order you need to have the column with its numeric value multiplied by (-1).
To attach it from the date table that was made with pq you can
1. duplicate the date column

Ritaf1983_0-1722082910901.png

2. change the data type to whole number:

Ritaf1983_1-1722082968418.png

3. multiply it by (-1)

Ritaf1983_2-1722083022381.pngRitaf1983_3-1722083050193.png

Close and apply, and modify the date sort by this new column from the table view :

Ritaf1983_4-1722083150655.png

Result :

Ritaf1983_5-1722083183691.png

Unfortunately, I couldn't find a solution for the scenario where you're using a calendar created with DAX. However, that type of calendar is less flexible. I recommend using a PQ calendar instead. I've attached a sample file for your reference. You can find more information about PQ calendars, including a script for creation, in the link.

https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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