Forum Discussion
Helper Iundefined2nd 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 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
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 __MaxDate2as 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
8 Replies
- DOLEARY85
Resident Rockstar
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 👍
poojashribangerthanks 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
- ThxAlot
Super User
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?
- Greg_Deckler
Community Champion
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
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 __MaxDate2as 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
