Forum Discussion

kasiaw29's avatar
kasiaw29
Resolver II
5 years ago
Solved

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 followi...
Need Help
Show and Tell
  • selimovd's avatar
    selimovd
    5 years ago

    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
     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

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