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 t...
  • 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

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

      • stzlee's avatar
        stzlee
        Frequent Visitor
        6 years ago

        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!!