Forum Discussion

stzlee's avatar
stzlee
Frequent Visitor
6 years ago
Solved

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

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's avatar
    az38
    6 years ago

    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

4 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      stzlee
      Frequent Visitor
      6 years ago

      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's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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