Find the most recent reinfection date

[Fred1983]

Good evening!

Some pateints can get the same disease multiple times and I am trying to find the most recent reinfection date. For each infection, I have the patient ID and episode date. How can I calcuate the last infection date, which is the most recent episode date before the current infection? 

I added an index by using patient ID (ascending) and episode date (oldest to newest) so cases are in the order I need.

I am think of finding the FIRSTNONBLANK of the episode date with a filter of patient ID=the current Patient ID and index < the current index, but I couldn't make it work

DtLastinfection = 

CALCULATE(FIRSTNONBLANK(table[DtEpisode], FILTER(table,table[PatientID]=current table[PatientID] && table[Index] < current table[Index])))

Please help, thank you in advance! 

[FreemanZ · Accepted solution]

hi @Fred1983 

try to add the calculated column like:

DtLastInfection=

MAXX(

    FILTER(

         TableName,          TableName[PatientID]=EARLIER(TableName[PatientID])&&TableName[DtEpisode]<EARLIER(TableName[DtEpisode])

    ),

    TableName[DtEpisode]

)

[Fred1983]

@FreemanZ Thank you so much!