Rank by Date with Values

[gclements]

Hi,

I am trying to get a rank by date, starting with the most recent date, and only for dates that have values.

Example below.  In the example I have a number of date, some of them have quantites.  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.

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.

Any help is much appreciated.

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

[mahoneypat · Accepted solution]

Here is one measure expression to try.  Change DateRank to the actual name of your table.

Rank if Qty =
VAR vThisRank =
    RANKX (
        FILTER (
            ALLSELECTED (
                DateRank[Date],
                DateRank[Quantity]
            ),
            DateRank[Quantity] > 0
        ),
        CALCULATE (
            MAX ( DateRank[Date] )
        ),
        ,
        DESC
    )
RETURN
    IF (
        SUM ( DateRank[Quantity] ) > 0,
        vThisRank,
        BLANK ()
    )

Regards,

Pat

[gclements]

Fantastic.  That did the trick, thank you very much!