Forum Discussion
Calculate difference between dates over multiple iterations
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.
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.
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 ) )
3 Replies
Yeah, what could possibly go wrong. What if AnotherApproval comes in before FirstApproval ? How do you know which events form a pair?
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 ) )Thank you! This worked perfectly.
