Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Aggregate rows and calculate duration

Hi all, I have my data model with columns connecting them marked as shown below. Attached is the pbi file for detail.

https://drive.google.com/file/d/1GTql5daw-9pXyxhGe0H73JrF-ZfBtjAv/view?usp=sharing

 

 

"operation_list" table contains list of manufacturing process (also called operation) of multiple orders. See below truncated table with two orders in it. "Start time" and "Finish time" are start and finish of each operation.

 

 

"routing_master" contains reference of operations for every order in "operation_list". Below sample for order 121817560. Operation that belong to the same grouping were assigned with the same "Opseq". 

 

The aim is to calculate the duration for each of the Opseq by subtracting the earliest "Start time" from latest "Finish time" on the "operation_list", subtract break time from it and present them in week-by-week in averaged basis by whatever orders belong to a given week. In form of pivot table, the result should be presented such below (numbers are for example only). Break time in day shift are 07:30 to 08:00 and 12:00 to 13:00, for night shift 19:30 to 20:00 and 00:00 to 01:00. 

 

WeekLead time per Opseq (days)
 1234
10.21.21.51.4
21.22.22.52.4
32.23.23.53.4
43.24.24.54.4

 

I tried the solution in Solved: Calculating Working hours - Microsoft Power BI Community yet I can't replicate it as my case is at different granularity.

 

Appreciate any helps you guys have! 

Need Help
Show and Tell
Tutorial Requests

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    According to your description, please try this code.

    Duration =
VAR _Diff =
    DATEDIFF (
        MINX (
            FILTER (
                ALL ( 'operation_list' ),
                'operation_list'[Order] = MAX ( 'operation_list'[Order] )
                    && RELATED ( routing_master[Opseq] ) = MAX ( 'routing_master'[Opseq] )
            ),
            'operation_list'[Start time]
        ),
        MAXX (
            FILTER (
                ALL ( 'operation_list' ),
                'operation_list'[Order] = MAX ( 'operation_list'[Order] )
                    && RELATED ( routing_master[Opseq] ) = MAX ( 'routing_master'[Opseq] )
            ),
            'operation_list'[Finish time]
        ),
        SECOND
    )
RETURN
    IF (
        _Diff = BLANK (),
        BLANK (),
        INT ( _Diff / 3600 ) & ":"
            & INT ( MOD ( _Diff, 3600 ) / 60 ) & ":"
            & MOD ( MOD ( _Diff, 3600 ), 60 )
    )

    Best Regards,
    Community Support Team _ kalyj

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