Downtime for a production line

[DIROPS]

I am working on a project to put together a report on dowtime to drive down to the production floor.  I have attached some sample data.  I would like to calculate the total time number of seconds between each Actual Time entry and totalize the number of occurances that are over 45 seconds but ignore anything that is shorter in duration.  Each 45 second occurance would be considered a downtime event.  I would then like to know the total elapsed time of downtime events.  Can anyone put me on the right path?https://villarifoodgroup-my.sharepoint.com/:x:/g/personal/cory_j_villaribros_com/EZ2HcMxdW9RPsqtO4V-...

[Greg_Deckler]

Perhaps you want something like my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[pabeader]

I can help get you strarted.  You will need to add an index column to your data.  You can do this either in the original data or as an added column when you import the data into Power BI.

You then use this index column to address the nth-1 and nth items and do your subtraction to get the elapsed time.  Just be careful at index 0.  You can't subrtact 1 for that one. 

From there you do your filtering and grouping as you need.

[DIROPS]

no problem on the index, the time difference is where I am stuggling.  I have found a ton of data on how to subtract the contents of one column from another and have been sucessful doing it however, I cant seem to figure out how to get the elapsed time from transaction to transaction with data in the same column, any thoughts

?

[pabeader]

To go further in my explination.  You would use the index+1 and index to point to values within your time column.  Those are the value that you are subtracting to get the elapsed time.  I think I think I have an example of this in DAX.  It might help to explain it better.

[DIROPS]

Yes please an example would help greatly if you  have one, appreciate the continued help!

[tarunsingla · Accepted solution]

Try this: Assuming your records are already sorted (by Import DateTime).

Create a calculated column, to get previous Import DateTime:

Previous Import DateTime = CALCULATE(MAX(Sheet1[ImportTime]), FILTER(Sheet1, Sheet1[ImportTime] < EARLIER(Sheet1[ImportTime])))

Add another calculated column to compute the difference in Import Time and Previous Import time:

Time Difference =

VAR differenceInSeconds = DATEDIFF(Sheet1[Previous Import DateTime],Sheet1[ImportTime], SECOND)

RETURN IF(differenceInSeconds > 45, differenceInSeconds, 0)

This will ignore any downtime less than 45 seconds. You can pull this column into any visual to get the desired results (to see what events had a downtime and for how many seconds).

Finally add a calculated measure to compute the total downtime in Hours:

Downtime In Hours = FORMAT(((SUM(Sheet1[Time Difference]) / 60)/60)/24, "HH:mm:ss")

 Hope this helps.

Regards,

Tarun

[pabeader]

Nice use of earlier! I’m still not comfortable with it, so I worked around it. I like your way better and am going to use it myself.