Forum Discussion
Resolver IIDate 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
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 column2. 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
Try to download my file and follow the steps / share link to your pbix and I will check...
You need to multiply it with (-1) not 1...
The column for sorting should have negative numbers...
8 Replies
santoshlearner2Hello 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
Super User
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
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 column2. 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
- 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
You need to multiply it with (-1) not 1...
The column for sorting should have negative numbers...
- 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