Use RankX function to calculate date rank per Fiscal Year per Entity ID

0
votes

I would like to use the RankX function (to use the Dense feature for ties) in order to rank the dates in a table per two columns: Fiscal Year and Entity ID.

(I guess it may be easier to concatenate the two columns and just sort by that but I'm having issues with my formula still)

Below is my sample data:

Sample

My invalid dax formula which returns all 1's is:

Fiscal Purchase Index = 
VAR a = 'Purchases'[Purchase_Date]
VAR b = 'Purchases'[Entity_ID]
var c = 'Purchases'[FISCAL_YEAR]
RETURN
    CALCULATE (
        RANKX ('Purchases', a,,1,Dense ),
        FILTER (all('Purchases'), 
            'Purchases'[Entity_ID] = b &&
            'Purchases'[FISCAL_YEAR] = c
        )
    )

Any help in fixing the rank formula would be very much appreciated. Thank you

1
powerbidaxpowerquery

1 Answers

0
votes

Use the following dax formula:

Fiscal Purchase Index = 

VAR __entity = 'Purchases'[Entity ID]
var __year = 'Purchases'[FISCAL YEAR]
var __table = FILTER ( all('Purchases'), 'Purchases'[Entity ID] = __entity && 'Purchases'[FISCAL YEAR] = __year )

var __result = 
RANKX(__table, Purchases[Date] ,, 1, Dense) 

RETURN __result

Hope it helps.