Calculate duration between 2 rows

[Anonymous]

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

[Mariusz]

Hi @Anonymous 

Please see the attached file with a solution.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[dax]

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 Zhi

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

[Anonymous]

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

[Greg_Deckler]

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/3395...

[Anonymous]

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,