topic Re: Calculate difference between dates over multiple iterations in DAX Commands and Tips

Calculate difference between dates over multiple iterations

mollycat — Wed, 05 Mar 2025 23:06:54 GMT

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.

Re: Calculate difference between dates over multiple iterations

lbendlin — Thu, 06 Mar 2025 01:13:55 GMT

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

Re: Calculate difference between dates over multiple iterations

MarkLaf — Thu, 06 Mar 2025 05:21:21 GMT

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 ) )

Re: Calculate difference between dates over multiple iterations

mollycat — Thu, 06 Mar 2025 15:21:01 GMT

Thank you! This worked perfectly.