Forum Discussion

gkakun's avatar
gkakun
Helper III
4 years ago
Solved

Event duration

Hi all,

 

I have a question, i have this table, and im trying to know how long spesific escalation was in each event type. For example- escalation 168 opened-->moved to on hold-->reopened---> moved to on hold again---> reoepened again and then closed.

 

i want to know for how long it was on hold and open? 

 

Thanks in advanced!  

 

 

 

Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi gkakun ,

     

    You are correct, I have made the contrary logic how long does it take to get from the previous status.

     

    Replace the calculations by:

    Duration = 
DATEDIFF (
    'Table'[Date Time],
    CALCULATE (
        MIN ( 'Table'[Date Time] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[EscalationID] = EARLIER ( 'Table'[EscalationID] )
                && 'Table'[Date Time] > EARLIER ( 'Table'[Date Time] )
        )
    ),
    DAY
) + 1

     

    try if  #"Added Index"[EscalationID]{[Index] + 1} = [EscalationID] then  Duration.Days( ( #"Added Index"[Date Time]{[Index] + 1}) - [Date Time]) + 1 else null otherwise null

     

     

     

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    Hi gkakun ,

     

    Try to add the following column to your model:

    Duration =
DATEDIFF (
    CALCULATE (
        MAX ( 'Table'[Date Time] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[EscalationID] = EARLIER ( 'Table'[EscalationID] )
                && 'Table'[Date Time] < EARLIER ( 'Table'[Date Time] )
        )
    ),
    'Table'[Date Time],
    DAY
)

    This is in days but you can the use it in hours months, and so on.

     

    Now just use the status to get the sum of that value:

     

    You can also do this on M Query:

    • Sort the table by EscalationID and then by Data time
    • Add an Index Column
    • Add the following calculated column:
    try if  #"Added Index"[EscalationID]{[Index] - 1} = [EscalationID] then  Duration.Days( ([Date Time] - #"Added Index"[Date Time]{[Index] - 1})) else null otherwise null

     

    Result below:

     

    Has you can see below both of the columns have similar results:

     

    The variations has to do with the time conversion on DAX and Power Query.

     

     

     

    • gkakun's avatar
      gkakun
      Helper III
      4 years ago

      Hi Friend,

       

      Thanks a lot for your help.

       

      The logic is not working well. For example, escalation number 168 was on hold from Feb 8 until Feb 13 (on hold, and then reopened, 5 days), and then was o hold from Feb 14 until March 16, and then reopened, and closedf on March 23 (32 days). Overall 37 days and not 10  

      • MFelix's avatar
        MFelix
        Super User
        4 years ago

        Hi gkakun ,

         

        You are correct, I have made the contrary logic how long does it take to get from the previous status.

         

        Replace the calculations by:

        Duration = 
DATEDIFF (
    'Table'[Date Time],
    CALCULATE (
        MIN ( 'Table'[Date Time] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[EscalationID] = EARLIER ( 'Table'[EscalationID] )
                && 'Table'[Date Time] > EARLIER ( 'Table'[Date Time] )
        )
    ),
    DAY
) + 1

         

        try if  #"Added Index"[EscalationID]{[Index] + 1} = [EscalationID] then  Duration.Days( ( #"Added Index"[Date Time]{[Index] + 1}) - [Date Time]) + 1 else null otherwise null