Forum Discussion

poojashribanger's avatar
poojashribanger
Icon for Helper I rankHelper I
2 years ago
Solved

undefined2nd max date based on ID and status

Hello All,   I am trying to get 2nd max date for each ID month wise . for eg for ID 1 in month Feb i should get 16-02-2022 for status closed and for march month it should be null as i don't have 2...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    poojashribanger Oh, I was assuming that you would have the ID and Status in context only. You could do the following as a calculated column:

     

    Column = 
  VAR __ID = [Id]
  VAR __Status = "Closed"
  VAR __MaxDate = MAXX(FILTER('Table', [Id] = __ID && [Status] = __Status), [date])
  VAR __MaxDate2 = MAXX(FILTER('Table', [Id] = __ID && [Status] = __Status && [date] < __MaxDate), [date])
RETURN
  __MaxDate2

     

    as a measure:

     

    Measure = 
  VAR __ID = MAX([Id])
  VAR __Status = "Closed"
  VAR __MaxDate = MAXX(FILTER('Table', [Id] = __ID && [Status] = __Status), [date])
  VAR __MaxDate2 = MAXX(FILTER('Table', [Id] = __ID && [Status] = __Status && [date] < __MaxDate), [date])
RETURN
  __MaxDate2

     

DOLEARY85's avatar
DOLEARY85
Icon for Resident Rockstar rankResident Rockstar
2 years ago

Hi,

 

You can do this in a couple of steps:

 

1. create a calculated column to rank the dates by month:

 

Rank =
RANKX(
   FILTER('Table', 'Table'[date].[Month] = EARLIER('Table'[date].[Month])),
   'Table'[date],
   ,
   ASC,
   Dense
)
 
2. create a measure to calculate on the ID and use the rank = 2:
 
SecondMaxDate =
CALCULATE(
   MAX('Table'[date]),
   FILTER(
      ALLEXCEPT('Table','Table'[Id]),
      'Table'[Rank] = 2
   )
)
 

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

  • poojashribanger's avatar
    poojashribanger
    Icon for Helper I rankHelper I
    2 years ago

    thanks for your response.

    But i need 2nd max date for only status =closed also is there any posibilities that this can be done in modelling tab as a column in fact table rather than measure