cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JoeCochran
Regular Visitor

Group by most recent date, then next most recent, etc

Hello! I'm trying to convert a set of disparate dates into numbers (or indicators - text is fine) indicating the most recent in a group, next most recent in a group, etc. For instance:

 

turn this:

StoreDate
A9/1/2019
A5/1/2019
A3/1/2019
A1/1/2019
B8/1/2019
B7/15/2019
B6/3/2019
B4/1/2019
C7/16/2019
C6/1/2019
C5/1/2019
C4/1/2019
D2/1/2019
D1/1/2019
D12/15/2018
D11/1/2018

 

into this:

StoreDate
A1
A2
A3
A4
B1
B2
B3
B4
C1
C2
C3
C4
D1
D2
D3
D4

 

I get the sense that this will be accomplished through the group by function - but I can't figure out how! I'm open to M or Dax solutions! Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Group by most recent date, then next most recent, etc

@JoeCochran 

 

You can rank the dates for each store

 

Calc Column =
RANKX ( FILTER ( TableName, [Store] = EARLIER ( [Store] ) ), [Date],, DESC )

 

Try my new Power BI game Cross the River

View solution in original post

1 REPLY 1
Super User III
Super User III

Re: Group by most recent date, then next most recent, etc

@JoeCochran 

 

You can rank the dates for each store

 

Calc Column =
RANKX ( FILTER ( TableName, [Store] = EARLIER ( [Store] ) ), [Date],, DESC )

 

Try my new Power BI game Cross the River

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors