Calculate time difference based on another column and it's previous/next row

Hi stzlee 

try a measure

Measure = 
var _lastNoTrip = CALCULATE(MAX(Table1[Time Local]),FILTER(ALL(Table1),Table1[Time Local]<MAX(Table1[Time Local]) && Table1[TripTypeTest]="NoTrip"))
var _lastFirstInTrip = CALCULATE(MIN(Table1[Time Local]),Table1[Time Local]>_lastNoTrip,Table1[TripTypeTest]="InTrip")
var _previousTime = CALCULATE(MAX(Table1[Time Local]),FILTER(ALL(Table1),Table1[Time Local]<MAX(Table1[Time Local])))
var _previousTripType = CALCULATE(FIRSTNONBLANK(Table1[TripTypeTest],1),Table1[Time Local]=_previousTime)
var _duration = DATEDIFF(_lastFirstInTrip,SELECTEDVALUE(Table1[Time Local]),SECOND)
var Hours = INT(_duration/3600)
var Minutes = INT(MOD(_duration - (Hours * 3600),3600)/60)
var Seconds = ROUNDUP(MOD(MOD(_duration - (Hours * 3600),3600),60),0)
var H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
var M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
var S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )

RETURN
if(_previousTripType="InTrip",CONCATENATE(H,CONCATENATE( ":", CONCATENATE(M,CONCATENATE(":", S)))),0)

big thx for a huge part of this solution for Greg_Deckler here https://community.powerbi.com/t5/DAX-Commands-and-Tips/Duration-in-DAX/td-p/57978

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn