undefined2nd max date based on ID and status

[poojashribanger]

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 2nd max date for closed status.

Can someone please help me on this.

Id	date	Status
1	15-02-2022	ready
1	16-02-2022	closed
1	17-02-2022	ready
1	18-02-2022	closed
2	22-03-2023	ready
2	23-03-2023	closed

Expected Output:

Id	date	Status	outputdate
1	15-02-2022	ready	16-02-2022
1	16-02-2022	closed	16-02-2022
1	17-02-2022	ready	16-02-2022
1	18-02-2022	closed	16-02-2022
2	22-03-2023	ready	null
2	23-03-2023	closed	null

Thanks & Regards,

Poojashri

[ThxAlot]

For fun only, a showcase of powerful worksheet formulas.

=INDEX(FILTER([Date],([Id]=[@Id])*(EOMONTH([Date]&"",0)=EOMONTH([@Date],0))*([Status]="closed")),2)

[poojashribanger]

Thanks , this is the output i am expecting but it should be 16thFeb(i.e 2nd max date of closed status) i want it to be done on Power BI .

Is it possible?

[DOLEARY85]

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]

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

[Greg_Deckler]

@poojashribanger Perhaps:

Measure = 
  VAR __MaxDate = MAX('Table'[date])
  VAR __MaxDate2 = MAXX(FILTER('Table', [date] < __MaxDate), [date])
RETURN
  __MaxDate2

[poojashribanger]

Thanks for the response Greg ,I tried this but this is not giving me the expected output.

i want the expected output like this

Id	date	Status	outputdate
1	15-02-2022	ready	16-02-2022
1	16-02-2022	closed	16-02-2022
1	17-02-2022	ready	16-02-2022
1	18-02-2022	closed	16-02-2022
2	22-03-2023	ready	null
2	23-03-2023	closed	null

[Greg_Deckler · Accepted solution]

@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

[poojashribanger]

Thanks Greg this seems to work for me.