cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkJames Member
Member

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:

 

01.PNG

 

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:

 

02.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

 @MarkJames

 

As a calculated column with DAX

 

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

View solution in original post

2 REPLIES 2
Super User
Super User

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

 @MarkJames

 

As a calculated column with DAX

 

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

View solution in original post

Highlighted
MarkJames Member
Member

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

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 40 members 979 guests
Please welcome our newest community members: