Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

calculate time between two columns and different rows - turnaround calculation

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 🙂

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi Anonymous 

     

    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.

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi Anonymous 

     

    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.