How to RANK by MAX date of each group without creating a second table?

[vendersonalias0]

My source data has a table with users and each sale they make is recorded as a row.

My goal is to find the latest date each user made a sale then rank those dates accordingly with the latest being 1.

Table Example

The formula I'm using to find the max date is working correctly

Max Date = CALCULATE(MAX('salestable'[Date]),ALLSELECTED('salestable'[Date]))

but my last sale measure is giving me the ranking relative to all dates rather than the max date of each group
my intended output should have:
rank 2 instead of rank 4,
rank 3 instead of rank 7 etc.

I believe it is ranking each group's max date vs all dates in the table instead of only the ranks by group.

Last Sale = RANKX(ALL('salestable'),[Max Date],,DESC,Dense)

I'm not sure if there is a better way to do this but the desired output is to have cards displaying the names of the last few users which made a sale. (In the real data, there are at most 2 sales / week)

My idea was to have a multi row card with all users filtered rank 1, the second card would be users filtered to rank 2 etc. up until 3-4. There is probably a better way to do this but this is what I came up with, any suggestions or improvements would be welcome too.

Thank you!

Happy Holidays!

[v-yiruan-msft]

Hi @vendersonalias0 ,

You can create a measure as below:

Last Sale =
RANKX (
    ALLSELECTED ( 'salestable' ),
    CALCULATE ( MAX ( 'salestable'[Date] ) ),
    ,
    DESC,
    DENSE
)

Best Regards

[parry2k]

@vendersonalias0 makes sense, I guess you used ALLEXCEPT ( Table, Table[User]), correct?

[vendersonalias0 · Accepted solution]

Thanks for the help guys, funny enough when I was creating a mock data set I happened to use ALLEXCEPT in the MAX DATE formula, then ALLSELECTED in the last date measure started to work... 

[parry2k]

@vendersonalias0 there are many ways to achieve this. I would recommend putting sample data in pbix and share thru one drive/google drive and will get you the solution.

[CNENFRNL]

@vendersonalias0 , frankly speaking, the complexity of RANKX() is way beyond one's expectation, especially when you include another measure in it; context transition occur twice during the evaluation of RANKX.

Unless you paste some dummy data or a pbix file, it's hard to author a correct measure that fits in your scenario.