Forum Discussion

RvdC's avatar
RvdC
Frequent Visitor
6 years ago
Solved

Calculating with DAX the total Interruption Time with overlapping intervals

I have a challenge with this case:   There is a technical installation which produces products on order. Only one order at the time. Each order has a unique ID. It starts with the starts order acti...
  • Anonymous's avatar
    Anonymous
    6 years ago

    RvdC ,

     

    Hey thanks for pointing that out.

     

    i had missed checking a gap in between interupption time.

     

    Hope this works.

     

     

    RvdC ,

     

    Thanks, There is a gap of 10 mins in between. I did not notice that.

     

    Hope this works.

     

     

    Calculated Columns

     

    End of Previous InteruptionID = IF (Table4[Activity] = "Start Interruption", CALCULATE(MAX(Table4[Date/Time End]),FILTER(ALLEXCEPT(Table4,Table4[Order_ID]), Table4[Interuption ID]<EARLIER(Table4[Interuption ID]) && Table4[Activity] = "Start Interruption")))

     

     

    Gaps in Between = IF ( Table4[End of Previous InteruptionID] < Table4[Date/Time], DATEDIFF(Table4[End of Previous InteruptionID], Table4[Date/Time],MINUTE))

     

     

     

    Measure

    Interuption Time (Orders) =

    var _addgaps = CALCULATE(SUM(Table4[Gaps in Between]), ALLEXCEPT(Table4, Table4[Order_ID]))
    var _StartTime = CALCULATE(MIN(Table4[Date/Time]),Filter(ALLEXCEPT(Table4,Table4[Order_ID]), Table4[Activity] = "Start Interruption"))
    var _EndTime = CALCULATE(MAX(Table4[Date/Time End]),Filter(ALLEXCEPT(Table4,Table4[Order_ID]), Table4[Activity] = "Start Interruption"))

    return
     
    DATEDIFF(_StartTime,_EndTime,MINUTE) - _addgaps
     
     
     
     
    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
RvdC's avatar
RvdC
Frequent Visitor
6 years ago

Hi Harsh,

 

Thanks for your solution! I have tested it, got the same results. It works almost perfect. The net dealy time for Case D is 75 minutes in stead of 85 minutes. The delay time is the 5 minutes from start I4 tot end I4 + the start from I5 untill the end of I7 (70 minutes). Makes a total of 75 minutes. The script adds somewhere 10 minutes to it. Do you have a solution for that?

Maybe I am overlooking something.

 

Thanks already for your support.

Regards,

Rob van de Coevering

Anonymous's avatar
Anonymous
Not applicable
6 years ago

RvdC ,

 

Hey thanks for pointing that out.

 

i had missed checking a gap in between interupption time.

 

Hope this works.

 

 

RvdC ,

 

Thanks, There is a gap of 10 mins in between. I did not notice that.

 

Hope this works.

 

 

Calculated Columns

 

End of Previous InteruptionID = IF (Table4[Activity] = "Start Interruption", CALCULATE(MAX(Table4[Date/Time End]),FILTER(ALLEXCEPT(Table4,Table4[Order_ID]), Table4[Interuption ID]<EARLIER(Table4[Interuption ID]) && Table4[Activity] = "Start Interruption")))

 

 

Gaps in Between = IF ( Table4[End of Previous InteruptionID] < Table4[Date/Time], DATEDIFF(Table4[End of Previous InteruptionID], Table4[Date/Time],MINUTE))

 

 

 

Measure

Interuption Time (Orders) =

var _addgaps = CALCULATE(SUM(Table4[Gaps in Between]), ALLEXCEPT(Table4, Table4[Order_ID]))
var _StartTime = CALCULATE(MIN(Table4[Date/Time]),Filter(ALLEXCEPT(Table4,Table4[Order_ID]), Table4[Activity] = "Start Interruption"))
var _EndTime = CALCULATE(MAX(Table4[Date/Time End]),Filter(ALLEXCEPT(Table4,Table4[Order_ID]), Table4[Activity] = "Start Interruption"))

return
 
DATEDIFF(_StartTime,_EndTime,MINUTE) - _addgaps
 
 
 
 
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
  • RvdC's avatar
    RvdC
    Frequent Visitor
    6 years ago

    Thanks a lot for your help! All works fine for me.

    Best,

    Rob