Date in Matrix Column how to sort ascending

[santoshlearner2]

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

[Ritaf1983 · Accepted solution]

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

2. change the data type to whole number:

3. multiply it by (-1)

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

Result :

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

[Ritaf1983 · Accepted solution]

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

[santoshlearner2]

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]

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.

[Ritaf1983 · Accepted solution]

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

[santoshlearner2]

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

[Ritaf1983 · Accepted solution]

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

[santoshlearner2]

Hi,

I got it, Problem solved.

Amazing

Warm Regards

Santosh

[Ritaf1983]

Happy to help 🙂

[Ritaf1983 · Accepted solution]

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

2. change the data type to whole number:

3. multiply it by (-1)

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

Result :

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