topic Re: Calculate time difference based on another column and it's previous/next row in DAX Commands and Tips

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

stzlee — Tue, 07 Jan 2020 23:22:46 GMT

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

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

az38 — Wed, 08 Jan 2020 07:51:47 GMT

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

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

stzlee — Thu, 09 Jan 2020 05:21:38 GMT

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!

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

az38 — Thu, 09 Jan 2020 16:16:04 GMT

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

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

stzlee — Fri, 10 Jan 2020 00:42:56 GMT

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