Forum Discussion
Aggregate rows then calculate duration
Hi all, my data model along with connecting columns are as shown below. Attached is the pbi 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!
Hi Anonymous
Here is the sample file with the solution https://www.dropbox.com/t/4LqnqA6a1sYn0JipNot 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
4 Replies
- tamerj1
Community Champion
Hi Anonymous
Here is the sample file with the solution https://www.dropbox.com/t/4LqnqA6a1sYn0JipNot 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 DurationHi tamerj1 , thank you for suggestion and it works as expected!
Pardon me for taking a while to mark this as a solution.
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.