Calculate duration between the max value and the second max value of the dates

[NhatMinh]

I'm a newbie with DAX.

My database is a little complicated, so I get stuck with it.

So in the visualization, I have Rows, including: Key -> Last Date.

In Columns, I have: Status 1, Status 2, and Duration.

There would be some events happen for a while, before changing the status from FAILED to SUCCESS in Status 1.

Then it takes a while to have the SUCCESS value in Status 2.

I must calculate the date diff between the Date Time value from Status 2 (when SUCCESS) and Status 1 (When SUCCESS).

Whenever I tried to calculate the 2nd max date, either I've got issue with the circular dependency or the group by issue, i.e. it gets the max value of the whole table, not from the group.

Could you please give me some hints on my problem?

Thanks so much for all of your help.

[lbendlin]

Is that really the format of your source data?  That's not usable for Power BI. Please confirm the true format of the source data.

[NhatMinh]

Here I've attached the sample data.
https://docs.google.com/spreadsheets/d/1H0Gikuxe1Ntr1DOxe35Y0Ty5fNCHU1WN/edit?usp=share_link&ouid=10... 

Here is my formula to calculate the Last Success Event:

Last Success Event =

var Last_date = CALCULATE ( MAX (Event[attempted_on]), ALLEXCEPT (Event, Event[Key1], Event[key2]) )

RETURN

CALCULATE (

MAX (Event[attempted_on]),

ALLEXCEPT (Event, Event[key1], Event[key2]),

Event[attempted_on] < Last_date

)

It keeps spinning, so I'm not sure if my formula to calculate the second max date time is correct or not.

I really appreciate your help. I don't know if it is because the database has millions of records caused the spinning issue or not.

Thanks a lot.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523