cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kasiaw29
Resolver II
Resolver II

How to find max, second, third latest date after max.

Hi there,

 

Wondering if anyone has attempted this before. I'm trying to find max date per group, second max date and then third. 

I'm okay with getting max and second to that by using the following: 

 

The calculation to find max date per group: 

Latest Date = CALCULATE(
                                      MAX('Table'[Date]),  ALLEXCEPT('Table','Table'[ID]))
The calculation to find a second latest date: 
2nd Latest Date =CALCULATE
                                     (MAX('Table'[Date]), ALLEXCEPT('Table','Table'[ID]),
                                                'Table'[Date] <> 'Table'[Latest Date])
 
Is there a way to find 3rd latest date? 
 
Thanks! 
1 ACCEPTED SOLUTION
selimovd
Super User
Super User

Hey @kasiaw29 ,

 

if that worked, then you can just repeat it:

3rd Latest Date =
CALCULATE(
    MAX( 'Table'[Date] ),
    ALLEXCEPT( 'Table', 'Table'[ID] ),
    'Table'[Date] <> [Latest Date] && 'Table'[Date] <> [2nd Latest Date]
)
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

 

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@kasiaw29 I'm thinking that you should do something like:

Measure = 
    VAR __Table = ADDCOLUMNS('Query1',"__Rank",RANKX('Query1',[Date],,DESC))
RETURN
    MAXX(FILTER(__Table,[__Rank] = 3),[Date])

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
selimovd
Super User
Super User

Hey @kasiaw29 ,

 

if that worked, then you can just repeat it:

3rd Latest Date =
CALCULATE(
    MAX( 'Table'[Date] ),
    ALLEXCEPT( 'Table', 'Table'[ID] ),
    'Table'[Date] <> [Latest Date] && 'Table'[Date] <> [2nd Latest Date]
)
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

 

Should have thought of that! Magic, worked perfectly! 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors