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

[stzlee]

Hi all,

I am somewhat new to DAX and Power BI in general, but I have made pseudocode for this problem and am having difficulty trying to convert this into DAX.

What I'm trying to achieve is to calculate the time difference when the subject is determined to be in a 'trip'. The values for whether it is in a trip or not is 'InTrip' and 'NoTrip' respectively. The complexity comes from the fact that if the subject is InTrip for only one time-stamp then the time in trip shall not be calculated (essentially requires to having a minimum of two InTrips consecutively). Here is an arbitrary example of what I mean:

One thing to note is that the counter would start from the bottom-most row. Unsure what the convention is in Power BI and perhaps I have to flip the Time Local column?

I have looked at other somewhat similar threads but I have not been able to alter their code/logic to be able to solve my problem :<

Thanks in advance!!

[az38 · Accepted solution]

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

[stzlee]

Thanks @az38. I have tried to understand the logic and have applied your provided measure to my data.

What I have found by changing the RETURN function to output "lastNoTrip", "previousTime", and "lastFirstInTrip" (for debugging purposes) provides the desired output for each.

However, when I revert it to H,M,and S; and try to display the measure as a column, the error of "Expressions that yield variant data-type cannot be used to define calculated columns." This was attempted by simply using:

newColumn = Table1[Measure]

To remedy this for debugging purposes, I have tried formatting the values from a number to a string. The result is just a column full of 0's. The DAX equation for this is:

newColumn = CONCATENATE(FORMAT(Table1[Measure], "General Number", "")

I have and will be attempting multiple other workarounds but eagerly wait your (or anyone's) response.

Thank you again in advance!

[az38]

@stzlee 

you could share your pbix-file without sensitive data for me, i will check

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

[stzlee]

Thanks for the offer @az38 .

After deleting a few of the sensitive data in preparation to share the pbix-file, the column shows the desired result!! Although the Visual shows a weird value, but I believe I can write a new Measure to get what I need.

I will try to troubleshoot why having multiple irrelevant columns have affected the result but I will approve your solution!

Thanks for everything!!