calculate time between two columns and different rows - turnaround calculation

[poogleshmoogle]

I have two columns:
In time and out time - the columns are formatted in date and time. I need to find the turnaround time in minutes (in time - previous out time). 

I also want the turnaround time for just the day, not between two dates. (on excel I was just doing less than 2 hours to be safe and then filtering) 

Another question based on the same data, so I would need to calculate the delay time(which is the first in time for the day - 8am), any idea on how to do that? For each day, have a new column that says the delay time.

Any help is appreciated! Thank you 🙂

[v-jingzhang · Accepted solution]

Hi @poogleshmoogle 

You can first add an Index column with Power Query Editor, then add a calculated column with below DAX. Change this TurnAround column to Decimal number data type. 

TurnAround = 
var _previousOutTime = MAXX(FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1),'Table'[Out Time])
var _previousOutDate = DATEVALUE(_previousOutTime)
return
IF(DATEVALUE('Table'[In Time])=_previousOutDate,'Table'[In Time]-_previousOutTime,BLANK())

For delay time, you can create the following column. Also change its data type to decimal number. 

Delay = 
var _firstInTime = MINX(FILTER('Table',DATEVALUE('Table'[In Time])=DATEVALUE(EARLIER('Table'[In Time]))),'Table'[In Time])
return
IF('Table'[In Time]=_firstInTime, _firstInTime-(DATEVALUE('Table'[In Time])+TIME(8,0,0)), BLANK())

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.