Forum Discussion
mollycat
Helper II
1 year agoCalculate 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 ma...
- 1 year ago
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 ) )
MarkLaf
Super User
1 year agoIt 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 )
)
- mollycat1 year ago
Helper II
Thank you! This worked perfectly.