Forum Discussion

MAZHAR's avatar
MAZHAR
Frequent Visitor
3 years ago
Solved

CALCLATE TIME BETWEN INTERVALS

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.  Total Time (Minutes) = (STAR...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

MAZHAR's avatar
MAZHAR
Frequent Visitor
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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.