Forum Discussion
Rankx with Dates
Hi all,
I have the table like that and want to create a report with a RankX
| Customer | Material | Order Date | RankX |
| A | 123 | 1.1.2019 | 1 |
| A | 123 | 1.1.2019 | 2 |
| A | 123 | 3.3.2019 | 3 |
| A | 145 | 10.1.2019 | 1 |
| A | 186 | 5.1.2019 | 1 |
| A | 186 | 7.1.2019 | 2 |
| B | 123 | 20.1.2019 | 2 |
| B | 123 | 9.1.2019 | 1 |
Can somebody help me with that?
Thanks
Christoph
Hi Anonymous ,
Create a Calculated Column
RANK Column = RANKX(FILTER('Table','Table'[Customer] = EARLIER('Table'[Customer]) && 'Table'[Material]= EARLIER('Table'[Material])),'Table'[Order_date],,ASC)Regards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
12 Replies
Hi Anonymous ,
Please check if the below measure works.
Ranking =RANKX(FILTER(Table3,Table3[Material] = EARLIER(Table3[Material])),Table3[Order Date],,ASC,Dense)If not, please share some more information on what do you want to rank i.e rank by material, customer. Also, what is the format of your date column.Regards,Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!!- amitchandak
Super User
For Rank Refer these links- These are one of the best you can refer
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale 
v-xicaiCommunity Support
Hi Anonymous ,
You may create rank using column or measure like DAX below.
Column: Rankx = RANKX(FILTER(Table, Table[Customer]=EARLIER(Table[Customer])&&Table[Material]=EARLIER(Table[Material])),Table[Order Date],,ASC ,Skip) Measure: Rankx = RANKX(FILTER(Table, Table[Customer]=MAX(Table[Customer])&&Table[Material]=MAX(Table[Material])), MAX(Table[Order Date]),,ASC ,Skip)Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi all,
both solutions are showing different numbers from what I expected.
Let me rephrase my request.
I have this table:
Format:
Soldto: 123/ABC
Material: 123/ABC
Order date: Whole number (I displayed the dates in the table below in date format)
Customer Material Order Date A 123 1.1.2019 A 123 2.2.2019 A 123 3.3.2019 A 145 10.1.2019 A 145 12.1.2019 A 186 7.1.2019 B 123 20.1.2019 B 123 9.1.2019 I want to create a report, which shows me the following.
A ranking (does need to be with formula rank) like this:
I want to see on (Customer & Product) level a ranking of the order dates.
For example
Customer A with Product 123 for order date: 1.1.2019 = 1
Customer A with Product 123 for order date: 2.2.2019 = 2
Customer A with Product 123 for order date: 3.3.2019 = 3
Customer A with Product 145 for order date: 10.1.2019 = 1
Customer A with Product 145 for order date: 12.1.2019 = 2
...
Do you understand what I mean?
BR
Lanko
Hi Anonymous ,
You can use the below meaures.
Ranking =var _a = MAX(Table1[Customer])var _b = MAX(Table1[Material])returnRANKX(FILTER(ALL(Table1[Material],Table1[Customer],Table1[Order Date]), Table1[Customer] = _a && Table1[Material] = _b), CALCULATE( MAX(Table1[Order Date])),,ASC ,Skip)Thanks and Regards,Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
