Rank by Date with Values

Question

Hi,&nbsp;I am trying to get a rank by date, starting with the most recent date, and only for dates that have values.&nbsp;Example below.&nbsp; In the example I have a number of date, some of them have quantites.&nbsp; For those that do have a quantity I would like to rank the date in descending order with the most recent date being rank 1.&nbsp;I do not know whether this should be a measure or a calculated column, but I do know that the values will change based on the filters that are applied, such as the customer.&nbsp;Any help is much appreciated.&nbsp;DateQuantityRank01/01/2020&nbsp;&nbsp;02/01/2020&nbsp;&nbsp;03/01/202010604/01/2020&nbsp;&nbsp;05/01/2020500506/01/2020&nbsp;&nbsp;07/01/2020&nbsp;&nbsp;08/01/2020&nbsp;&nbsp;09/01/20203410/01/2020&nbsp;&nbsp;11/01/2020&nbsp;&nbsp;12/01/202055313/01/202043214/01/2020101115/01/2020&nbsp;&nbsp;16/01/2020&nbsp;&nbsp;

mahoneypat · Accepted Answer

Here is one measure expression to try.&nbsp; Change DateRank to the actual name of your table.
&nbsp;
Rank&nbsp;if&nbsp;Qty&nbsp;=VAR&nbsp;vThisRank&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;RANKX&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DateRank[Date],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DateRank[Quantity]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DateRank[Quantity]&nbsp;&gt;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MAX&nbsp;(&nbsp;DateRank[Date]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DESC&nbsp;&nbsp;&nbsp;&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;DateRank[Quantity]&nbsp;)&nbsp;&gt;&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vThisRank,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BLANK&nbsp;()&nbsp;&nbsp;&nbsp;&nbsp;)
&nbsp;
&nbsp;
Regards,
Pat

Forum Discussion

Rank by Date with Values

2 Replies

Related content

Duplicate ranking

Rank with conditions

Lookup rank value?

Dynamic Ranking

Ranking KPI card

Date	Quantity	Rank
01/01/2020
02/01/2020
03/01/2020	10	6
04/01/2020
05/01/2020	500	5
06/01/2020
07/01/2020
08/01/2020
09/01/2020	3	4
10/01/2020
11/01/2020
12/01/2020	55	3
13/01/2020	43	2
14/01/2020	101	1
15/01/2020
16/01/2020