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. 

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

 

  • 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.

4 Replies

    • MAZHAR's avatar
      MAZHAR
      Frequent Visitor
      3 years ago

      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
  • 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.