cancel
Showing results for
Did you mean:
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
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

3 REPLIES 3
Super User

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

``````Measure =
RETURN
MAXX(FILTER(__Table,[__Rank] = 3),[Date])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
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

Resolver II

Should have thought of that! Magic, worked perfectly!

Announcements