CALCLATE TIME BETWEN INTERVALS

[MAZHAR]

I have a large Excel sheet like this where Document Numbers will be listed along with all other information as in the picture. I want to calculate the following. 

1. Total Time (Minutes) = (START-END)
2. Break Time (Minutes) = (RESUME- PP)

[MAZHAR]

I tried the following solution which is not giving me the exact total time on one Document Number, it gives me accurate time on the last level which is SCP_LINE but on combining all it fails.

I created three measures to calculate Total Time

START TIME = CALCULATE(MAX(MCH_WorkStatusD[Time]), MCH_WorkStatusD[CONDITION] = "START")

END TIME = CALCULATE(MAX(MCH_WorkStatusD[Time]), MCH_WorkStatusD[CONDITION] = "END")

STAGE = DATEDIFF([START TIME],[END TIME],MINUTE)

[Anonymous · Accepted solution]

Hi @MAZHAR ,

I suggest you to create  virtual table in your calculation and then use SUMX to achieve your goal.

Total Time =
VAR _SUMMARIZE =
    SUMMARIZE (
        'MCH_WorkStatusD',
        MCH_WorkStatusD[Line],
        MCH_WorkStatusD[SCP_Line],
        "Diff",
            VAR _STATR =
                CALCULATE (
                    MAX ( MCH_WorkStatusD[Time] ),
                    MCH_WorkStatusD[CONDITION] = "START"
                )
            VAR _END =
                CALCULATE ( MAX ( MCH_WorkStatusD[Time] ), MCH_WorkStatusD[CONDITION] = "END" )
            RETURN
                DATEDIFF ( _STATR, _END, MINUTE )
    )
RETURN
    SUMX ( _SUMMARIZE, [Diff] )

Result is as below.

Best Regards,
Rico Zhou

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

[tamerj1]

Hi @MAZHAR 
How many poses there could be? Or just once?

[MAZHAR]

@tamerj1 Thanks for your reply it is 5 the moment I just share an example of one.

1. PP
2. BRK
3. MSC
4. 1x2x