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

[kasiaw29]

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! 

[Greg_Deckler]

@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])

[selimovd · Accepted solution]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[kasiaw29]

Should have thought of that! Magic, worked perfectly!