Rank Measure with ID and Date

[adnanarain]

Hi All,

Below is the sample data:

ID	Date	Quantity
1	01/01/20	2
1	01/02/20	3
1	01/03/20	1
2	01/01/20	3
2	01/02/20	7
2	01/03/20	9
3	01/04/20	3
3	01/05/20	1
3	01/06/20	2

below is the output I need: Date Slicer dates selected from 1/2/2020 to 1/6/2020

ID	Date	Quantity	Rank
1	01/02/20	3	1
1	01/03/20	1	2
2	01/02/20	7	1
2	01/03/20	9	2
3	01/04/20	3	1
3	01/05/20	1	2
3	01/06/20	2	3

I want to create a measure to display rank within ID category and rank start from the oldest date selected through a slicer.

[amitchandak]

@adnanarain , try like

rankx(allselected(Table[Date]), calculate(sum(Table[Quantity])),,desc,dense)

Please find the attached file after the signature.

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[adnanarain]

@amitchandak This is what I want.

[V-pazhen-msft · Accepted solution]

@adnanarain 

Try this one:

Measure = RANKX(FILTER(ALLSELECTED('Table'),[ID]=MAX('Table'[ID])),CALCULATE(SUM([Date])),,ASC,Dense)

 

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

[adnanarain]

Thanks, Amit for your reply. This ranking is based on date and quantity so basically I need rank based on ID and date no matter what is the quantity, so for example, if we have ID 1, the date is 1st January and the quantity is 5 and in 2nd row, we have ID 1, date 2nd January and quantity is 8 then the rank will be 1 for 1st January and 2 for 2nd January. No matter what is the quantity.

I have tried to clear my point here I hope is clear now.