Forum Discussion

64653463456greg's avatar
64653463456greg
Frequent Visitor
4 years ago
Solved

Time between operations

 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.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
    64653463456greg
    4 years ago

    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.

3 Replies

  • 64653463456greg's avatar
    64653463456greg
    Frequent Visitor
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi 64653463456greg ,

       

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

       

      Best Regards,

      Stephen Tao

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.