Help with tracking the duration of multiple status changes

[pgiorgi1]

I have a data base that contains a number of assets and status changes, here is the data.

assetUUID	Status	timeHappenedUTC
d150	Take Action	6/3/2021 1:36
d151	Take Action	6/3/2021 1:36
d152	Take Action	6/3/2021 1:38
d150	Take Action	6/3/2021 1:43
d151	SOS	6/30/2021 22:50
d151	SOS Cancel	6/30/2021 22:50
d150	SOS	8/26/2021 3:18
d150	Take Action	8/26/2021 3:24
d150	SOS Cancel	8/26/2021 3:28

my first target was to create a duration formula between "SOS" and "SOS Cancel", this I have done.

My second request was to track the duration between  "SOS" and  "Take Action" status but there are more "Take Action" Status than  the "SOS" Status.

Is it possible to calculate a duration between "SOS" and "Take Action", only if it falls between "SOS" and "SOS Cancel" ?

I have no clue where to even begin to build something like this, 

I was also thinking there are other status changes that are different than "Take Action", is it possible to build something that would calculate a duration by using "Take Action" status then any status that happens before the "Take Action"?

I'm just begenning to get my head around DAX and starting to get some good results but this has me completly stumped.

[pgiorgi1]

O.K. thank you for the forumula it makes allot of sense, now I've tried it however my results are showing up with all no results  the durations are coming up with zero as the results, I tried to change the duration to seconds from minutes and there are no duration numbers associated to it.   I'll keep working on it.

[ERD]

Hi @pgiorgi1 ,

You can try the measures below for 2 cases you've described:

1. calculate a duration between "SOS" and "Take Action", only if it falls between "SOS" and "SOS Cancel"

2. calculate a duration by using "Take Action" status then any (other) status that happens before the "Take Action"

duration#1 = 
VAR c_asset = SELECTEDVALUE ( T[assetUUID] )
VAR c_status = SELECTEDVALUE ( T[Status] )
VAR c_time = SELECTEDVALUE ( T[timeHappenedUTC] )
VAR prev_time =
    CALCULATE (
        MAX ( T[timeHappenedUTC] ),
        FILTER (
            ALLSELECTED ( T ),
            T[timeHappenedUTC] < c_time && T[assetUUID] = c_asset
        )
    )
VAR prev_status =
    CALCULATE (
        MAX ( T[Status] ),
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( T ),
                T[timeHappenedUTC] < c_time && T[assetUUID] = c_asset
            ),
            T[timeHappenedUTC]
        )
    )
VAR next_status =
    CALCULATE (
        MAX ( T[Status] ),
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( T ),
                T[timeHappenedUTC] > c_time && T[assetUUID] = c_asset
            ),
            T[timeHappenedUTC]
        )
    )
RETURN
    IF (
        c_status = "Take action" && prev_status = "SOS" && next_status = "SOS Cancel",
        DATEDIFF ( prev_time, c_time, MINUTE ),
        0
    )

duration#2 = 
VAR c_asset = SELECTEDVALUE ( T[assetUUID] )
VAR c_status = SELECTEDVALUE ( T[Status] )
VAR c_time = SELECTEDVALUE ( T[timeHappenedUTC] )
VAR prev_time =
    CALCULATE (
        MAX ( T[timeHappenedUTC] ),
        FILTER (
            ALLSELECTED ( T ),
            T[timeHappenedUTC] < c_time && T[assetUUID] = c_asset
        )
    )
VAR prev_status =
    CALCULATE (
        MAX ( T[Status] ),
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( T ),
                T[timeHappenedUTC] < c_time && T[assetUUID] = c_asset
            ),
            T[timeHappenedUTC]
        )
    )
RETURN
    IF (
        c_status = "Take action" && prev_status <> "Take action",
        DATEDIFF ( prev_time, c_time, MINUTE ),
        0
    )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.