Calculate difference between dates over multiple iterations

[mollycat]

Hello! I am trying to calculate the duration (in minutes) that it takes for a record to go from one status to another (only for certain Status values). The first status change I'm interested in is marked as "FirstRequest" to "FirstApproval", but after that, the record can have multiple instances of "AnotherRequest" to "AnotherApproval". Each set (per Record Number) should be calculated independently. For those rows where the statuses do not apply to this duration, the EXPECTED DURATION should be null.

I've gotten to the point where I'm calculating the duration between each status change for a given Record Number with this formula, but am unsure how to implement the logic for the different Status types.

VAR _prevstatusdate_ = CALCULATE(MAX('Table'[Time Status was Set]),FILTER('Table','Table'[Time Status was Set] < EARLIER('Table'[Time Status was Set]) && 'Table'[Record Number] = EARLIER('Table'[Record Number])))RETURN DATEDIFF(_prevstatusdate_ ,'Table'[Time Status was Set],MINUTE)

Any help with this calculation would be greatly appreciated!

Record Number	Status	Time Status was Set	EXPECTED DURATION (minutes)
1	Draft	1/1/2025        8:00 AM
1	FirstRequest	1/1/2025      10:00 AM
1	FirstApproval	1/1/2025      10:30 AM	30
1	Reverted	1/2/2025        8:00 AM
1	AnotherRequest	1/2/2025        9:00 AM
1	Reviewed	1/2/2025        9:45 AM
1	AnotherApproval	1/2/2025      10:00 AM	60
1	Reverted	1/3/2025        8:00 AM
1	AnotherRequest	1/3/2025      12:00 PM
1	AnotherApproval	1/3/2025        3:00 PM	180
2	FirstRequest*	1/1/2025      10:00 AM
2	Reverted	1/1/2025      11:00 AM
2	FirstRequest	1/1/2025      11:30 AM
2	FirstApproval	1/1/2025      11:45 AM	15

*Note: this "FirstRequest" at 10:00 AM is effectively ignored. The EXPECTED DURATION associated with the 11:45 AM "FirstApproval" should start from the most recent "FirstRequest" at 11:30 AM.

[MarkLaf · Accepted solution]

It seems like you are looking for: CONTAINSSTRING( 'Table'[Status], "request" )

I think you can just incorporate that into your current formula to get what you want.

You may want to consider incorporating variables rather than use EARLIER to make your DAX a little more readable. Eg

Duration (min) = 
IF( 
    CONTAINSSTRING( 'Table'[Status] , "approval" ), //ignore unless we are in approval row
    VAR _thisTime = 'Table'[Time Status was Set] 
    VAR _thisRec = 'Table'[Record Number]
    VAR _prevReqs = FILTER( 
        ALL( 'Table'[Record Number], 'Table'[Status], 'Table'[Time Status was Set] ), 
        'Table'[Record Number] = _thisRec
        && 'Table'[Time Status was Set] < _thisTime 
        && CONTAINSSTRING( 'Table'[Status], "request" ) 
    )
    VAR _lastReqTime = CALCULATE( MAX( 'Table'[Time Status was Set] ), _prevReqs, REMOVEFILTERS( 'Table' ) )
    RETURN
    DATEDIFF( _lastReqTime, _thisTime, MINUTE )
)

[mollycat]

Thank you! This worked perfectly.

[lbendlin]

Yeah, what could possibly go wrong. What if AnotherApproval comes in before FirstApproval ? How do you know which events form a pair?