Forum Discussion

mollycat's avatar
mollycat
Icon for Helper II rankHelper II
1 year ago
Solved

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 ma...
  • MarkLaf's avatar
    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 )
    )