Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
poogleshmoogle
Frequent Visitor

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 🙂

Screenshot (15).png

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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())

 

vjingzhang_0-1669794253019.png

 

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())

 

vjingzhang_1-1669794896120.png

 

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

View solution in original post

1 REPLY 1
v-jingzhang
Community Support
Community Support

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())

 

vjingzhang_0-1669794253019.png

 

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())

 

vjingzhang_1-1669794896120.png

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors