Forum Discussion
poojashribanger
Helper I
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 2...
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
Greg_Deckler
Community Champion
Community Championpoojashribanger 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
poojashribangerHelper I
Helper IThanks Greg this seems to work for me.