Forum Discussion
Rank Dates within a Category or Group - DAX or Power Query
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
Anonymous
As a calculated column with DAX
RANK=RANKX(fILTER(Table1,[Category]=EARLIER([Category])),[Date],,DESC,dENSE)
4 Replies
- Zubair_Muhammad
Community Champion
Anonymous
As a calculated column with DAX
RANK=RANKX(fILTER(Table1,[Category]=EARLIER([Category])),[Date],,DESC,dENSE)
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
This is exactly what I was looking for. Thanks Zubair_Muhammad
