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
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    The result is showing numbers in minute.

    Please check the below picture and the attached pbix file.

     

     

    Turn Around CC =
VAR _currentdate =
    FORMAT ( Data[In Time], "yyyy-mm-dd" )
VAR _prevouttime =
    MAXX (
        FILTER (
            Data,
            FORMAT ( Data[In Time], "yyyy-mm-dd" )
                = FORMAT ( EARLIER ( Data[In Time] ), "yyyy-mm-dd" )
                && Data[Out Time] < EARLIER ( Data[Out Time] )
        ),
        Data[Out Time]
    )
VAR _result =
    DATEDIFF ( _prevouttime, Data[In Time], MINUTE )
RETURN
    IF ( NOT ISBLANK ( _prevouttime ), _result )

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    The result is showing numbers in minute.

    Please check the below picture and the attached pbix file.

     

     

    Turn Around CC =
VAR _currentdate =
    FORMAT ( Data[In Time], "yyyy-mm-dd" )
VAR _prevouttime =
    MAXX (
        FILTER (
            Data,
            FORMAT ( Data[In Time], "yyyy-mm-dd" )
                = FORMAT ( EARLIER ( Data[In Time] ), "yyyy-mm-dd" )
                && Data[Out Time] < EARLIER ( Data[Out Time] )
        ),
        Data[Out Time]
    )
VAR _result =
    DATEDIFF ( _prevouttime, Data[In Time], MINUTE )
RETURN
    IF ( NOT ISBLANK ( _prevouttime ), _result )