Forum Discussion

kasiaw29's avatar
kasiaw29
Icon for Resolver II rankResolver 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 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! 
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
     

     

3 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    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
     

     

    • kasiaw29's avatar
      kasiaw29
      Icon for Resolver II rankResolver II
      5 years ago

      Should have thought of that! Magic, worked perfectly! 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity 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])