Forum Discussion
Calculate duration between 2 rows
Hello,
I want to be able to establish the time between events for the same Work order and apply this delay to the next work center. Here is an example of the data and what I want to do :
Initial work table
| Work order | Date | Hour | Work center | Operation |
| 123 | 2020-05-09 | 11:00 | A1 | 10 |
| 123 | 2020-05-10 | 13:00 | A2 | 20 |
| 123 | 2020-05-11 | 15:00 | A3 | 30 |
| 123 | 2020-05-12 | 17:00 | A1 | 40 |
| 456 | 2020-05-07 | 11:00 | A1 | 30 |
| 456 | 2020-05-10 | 13:00 | A2 | 40 |
| 456 | 2020-05-12 | 15:00 | A3 | 50 |
Here's what I want after
| Work order | Date | Hour | Work center | Operation | Duration between | Next work center | Next operation |
| 123 | 2020-05-09 | 11:00 | A1 | 10 | 26:00 | A2 | 20 |
| 123 | 2020-05-10 | 13:00 | A2 | 20 | 26:00 | A3 | 30 |
| 123 | 2020-05-11 | 15:00 | A3 | 30 | 26:00 | A1 | 40 |
| 123 | 2020-05-12 | 17:00 | A1 | 40 | #N/A | #N/A | #N/A |
| 456 | 2020-05-07 | 11:00 | A1 | 30 | 74:00 | A2 | 40 |
| 456 | 2020-05-10 | 13:00 | A2 | 40 | 50:00 | A3 | 50 |
| 456 | 2020-05-12 | 15:00 | A3 | 50 | #N/A | #N/A | #N/A |
I'll then be able to evaluate the average delay of each part between each operation / work center
| Part# | Next work center | Next operation | Average of Duration between |
| A1S | A1 | 40 | 26:00 |
| A1S | A2 | 20 | 50:00 |
| A1S | A3 | 30 | 38:00 |
| Total A1S | 40:24 | ||
| Total | 40:24 |
I know I can do it in Excel, but can this be done with Power BI ? I have 1,200,000 rows of data to analyse.
I can easily get the min and the max to calculate the total duration of each work order, but I need to drill down to the workcenter.
Thanks!
Dominic
5 Replies
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
Thanks Greg, your MTBF calculation is really nice. I will surely use it.
If I understand correctly, it would gives me the average time after an event. In my case it's a little bit more complicated since I wanted to establish the time before the event.
I need to find out for each row to which work center the part is going in order to use your calculations. I can then make the average based on the "Next work center"
Regards,
Hi Anonymous ,
You could refer to my sample for details. In addition, I don't understand the logic of second result table, could you please explain this to me?
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The second result table comes from the average of the 1st result table. For exemple, the first work order when to the work center A2 2 times and waited 26 hours the 1st time and 74 hours the 2nd time, which gives an average of 50 hours.
Also, I can't open your .pbix since I'm only using the Power Pivot complement in Excel.
Part# Next work center Next operation Average of Duration between A1S A1 40 26:00 Average A1 40 26:00 A2 20 26:00 A2 20 74:00 Average A2 20 50:00 A3 30 26:00 A3 30 50:00 Average A3 30 38:00 Total A1S 40:24 Total 40:24 Dominic
