Power BI DAX: how to pull second-most recent date

0
votes

I'm trying to pull from a table of email opens, the dates for when someone becomes inactive (no opens in 90 days) and when they reactivate (the date of an open ending a period of inactivity).

I already was able to pull the most recent reactivation date by using the below dax formula:

Reactivation Date 1 = MAXX(RELATEDTABLE('Opens Table'), 'Opens Table'[Reactivation Date (90 days)])

I already have a populated Opens Table and I have a list of users in a User Table. Desired Output

1
powerbidax

1 Answers

0
votes

I am not sure if this is going to work in your particular scenario, but you can give it a shot:

Measure = 
VAR Date1 = MAX(Table[Date])
VAR Date2 = CALCULATE(MAX(Table[Date]),FILTER(Table,Table[Date]<Date1))
RETURN Date2

I am using a variable to filter off the most recent date. Hope this helps.

Edit:

Measure = 
VAR Date1 = CALCULATE(MAX(Table[Date]),ALLEXCEPT(Table,Table[User]))
VAR Date2 = CALCULATE(MAX(Table[Date]),ALLEXCEPT(Table,Table[User]),FILTER(Table,Table[Date]<Date1))
RETURN Date2