Aggregate rows then calculate duration

[Anonymous]

Hi all, my data model along with connecting columns are as shown below. Attached is the pbi file.

link to file 

Each row in operation_list represents a manufacturing process (also called operation) completed on a given order. Below shows truncated table with two orders on it.

routing_master is the "blueprint" for each order operations on operation_list. Below shown reference for order 121817560 from above. See routing_master[Opseq] column, operation that belong to the same "group" are assigned with the same number e.g., operation 900 to 1500 assigned with Opseq 4.

The aim (which is also my question) is how to calculate the duration of each Opseq by subtracting earliest "Start time" from latest "Finish time" of all operations that belong to the same Opseq, subtract break hours from it, and present its average over the week. In table form the final result should look like below. Break hours are: Day (07:30 to 08:00 and 12:00 to 13:00) and Night shift (19:30 to 20:00 and 00:00 to 01:00)

Week	Lead time per Opseq (days)
	1	2	3	4
1	0.2	1.2	1.5	1.4
2	1.2	2.2	2.5	2.4
3	2.2	3.2	3.5	3.4
4	3.2	4.2	4.5	4.4

I don't see how solution from Solved: Calculating Working hours - Microsoft Power BI Community can be implemented since the duration calculation was made as calculated table whereas my case need to aggregate first. I had tried the alternative to merge operation_list with routing_master, group by Opseq, and calculate the duration from there but it just takes so much time when data is refreshed.

So help a friend? Appreciate all suggestions!

[v-rongtiep-msft]

Hi @Anonymous ,

Does that make sense? If so, kindly mark @tamerj1 's  answer as the solution to close the case please. Thanks in advance.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

It does and thanks for the reminder! 👍

[tamerj1 · Accepted solution]

Hi @Anonymous 
Here is the sample file with the solution https://www.dropbox.com/t/4LqnqA6a1sYn0Jip

Not sure if the is what you actually need. Please have a look and let me know if you have any further queries.

Duration = 
VAR FirstStart = MIN ( operation_list[Start time] )
VAR LastEnd = MAX ( operation_list[Finish time] )
VAR Duration = DIVIDE ( DATEDIFF ( FirstStart, LastEnd, SECOND ), 86400 )
RETURN
    Duration

[Anonymous]

Hi @tamerj1 , thank you for suggestion and it works as expected!

Pardon me for taking a while to mark this as a solution.