Rank Dates within a Category or Group - DAX or Power Query

[Anonymous]

Hi,

I'm looking to rank my data to find the most recent information for each category.

Here's a brief example of the data I'm working with:

I have a Category column, a Date column and a Value column - the value column is irrelevant for what I want to achieve but I've included it so you can see where there are duplicate dates for the same Category. I don't need the Value column to be sorted or included in the Ranking.

This is what I'm looking to achieve:

I want the most recent Date for each Category to have the lowest number. If there are multiple instances of the same date they should all get the same rank. Then the date after this should get the next sequential number so there are no gaps - 1, 2, 3, etc. I'm looking for suggestions in either DAX or Power Query.

Can anyone help?

Thanks,

MarkJames

[Zubair_Muhammad · Accepted solution]

 @Anonymous

As a calculated column with DAX

RANK=RANKX(fILTER(Table1,[Category]=EARLIER([Category])),[Date],,DESC,dENSE)

[Anonymous]

Hi Zubair,
Can you please advise how we can make this rank dynamic, meaning it will auto populate based on date range selected. Right now, even if you filter a later date out, it will still show the same rank.

[Elvos]

Hi, what if you get a D category in one of the later dates and want to rank it accordingly to that date rank in the other categories?

BR

[Anonymous]

This is exactly what I was looking for. Thanks @Zubair_Muhammad