Time between operations

[64653463456greg]

 I have a table with dates and times (see picture).

The times represent an uptime for a component.

What I’d like to do is figure out a way to get a column of the downtime between operations.

So, if a part is operational between 10:41 and 11:46 and then again between 12:43 and 13:44. Then what is the downtime between the two operations.

What I also can’t figure out is how to exclude the times where the part has downtime overnight.

Can I filter that out in some way?

All of this must correspond to a serial number that I have several of in a separate column.

Basically:

Serial Number 1	Start Date 1	End Date 1	Start Time	End Time
Serial Number 1	Start Date 1	End Date 1	Start Time	End Time
Serial Number 1	Start Date 1	End Date 1	Start Time	End Time
Serial Number 1	Start Date 2	End Date 2	Start Time	End Time
Serial Number 1	Start Date 2	End Date 2	Start Time	End Time
Serial Number 1	Start Date 2	End Date 2	Start Time	End Time
Serial Number 2	Start Date 1	End Date 1	Start Time	End Time
Serial Number 2	Start Date 1	End Date 1	Start Time	End Time
Serial Number 2	Start Date 2	End Date 2	Start Time	End Time
Serial Number 2	Start Date 2	End Date 2	Start Time	End Time

And so on, and so on.

Any help on the matter is greatly appreciated.

[Anonymous · Accepted solution]

Hi @64653463456greg ,

It sounds like you want to subtract the start time of the current row from the end time of the previous row to get the intermediate downtime. Also group by Serial Number.

You could create a calculated column like

previous end time =
MINX (
    FILTER (
        'Table',
        [Serial Number] = EARLIER ( 'Table'[Serial Number] )
            && [End Time] > EARLIER ( 'Table'[End Time] )
    ),
    [End Time]
)

Then create another calculated column to get the time.

Time =
IF (
    NOT ( ISBLANK ( [previous end time] ) ),
    [previous end time] - [Start Time]
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[64653463456greg · Accepted solution]

Thank You Stephen for your answer.

I found another way that worked great as well.

What I did was:

Create an Index column.

Then based on that I made an “Next start time” table:

Next Start Time = IF('Data'[Serial #] = LOOKUPVALUE('Data'[Serial #],'Data'[Index],'Data'[Index]+1),LOOKUPVALUE('Data'[Start Time],'Data'[Index],'Data'[Index]+1),BLANK())

Then I made a column based on that for the downtime in minutes:

Down Time (Minutes) = (DATEDIFF('Data'[End Time],'Data'[Next Start Time],MINUTE)+0) 

This worked great for my need.

Thank you for taking the time and replying, I really appreciate it.

[Anonymous]

Hi @64653463456greg ,

Very good approach, if you could, please mark the solution, more people will benefit.😁

Best Regards,

Stephen Tao

[Anonymous · Accepted solution]

Hi @64653463456greg ,

It sounds like you want to subtract the start time of the current row from the end time of the previous row to get the intermediate downtime. Also group by Serial Number.

You could create a calculated column like

previous end time =
MINX (
    FILTER (
        'Table',
        [Serial Number] = EARLIER ( 'Table'[Serial Number] )
            && [End Time] > EARLIER ( 'Table'[End Time] )
    ),
    [End Time]
)

Then create another calculated column to get the time.

Time =
IF (
    NOT ( ISBLANK ( [previous end time] ) ),
    [previous end time] - [Start Time]
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.