Forum Discussion

Matic20's avatar
Matic20
Frequent Visitor
6 years ago
Solved

Difference btn dates with many dependences

TimeTicket IdAgentState
1/1/2020 13:4826625Chris  Herbertcreated
1/2/2020 1:1326625Chris  Herbertpaused
1/2/2020 14:2326625Chris  Herbertresumed
1/2/2020 14:2526625Chris  Herbertclosed
1/2/2020 14:3026625Chris  Herbertreopened
1/2/2020 14:3226625Chris  Herbertclosed
1/1/2020 14:2226626Chris  Herbertcreated
1/2/2020 1:1326626Chris  Herbertpaused
1/2/2020 14:2326626Chris  Herbertresumed
1/2/2020 14:2526626Chris  Herbertclosed
1/2/2020 5:3926628Chris  Herbertcreated
1/2/2020 5:4126628Chris  Herbertpaused
1/3/2020 3:4926628Chris  Herbertresumed
1/3/2020 3:5526628Chris  Herbertclosed
1/2/2020 6:0826629Omar  Salamacreated
1/2/2020 6:2626629Omar  Salamapaused
1/2/2020 7:0626629Omar  Salamaresumed
1/2/2020 7:0726629Omar  Salamaclosed
1/2/2020 9:0726634Anmar  Raoufcreated
1/2/2020 9:0926634Anmar  Raoufpaused
1/2/2020 9:4726634Anmar  Raoufresumed
1/6/2020 8:4326634Anmar  Raoufclosed

 

Expected Result(Minutes)
Ticket ID Earliest Closed-Created_DateResumed- PausedLast Closed-ReopenedAchieved SLAAgent
2662514477552694Chris  Herbert
2662614437900653Chris  Herbert
266281336132808Chris  Herbert
266295940019Omar  Salama
2663457363805698Anmar  Raouf

 

Achieved SLA = (Earliest_Closed - DateCreated) - (ResumedDate - PausedDate) + (Last_ClosedDate - ReopenedDate)

Need Help

13 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    This will give diff between current and next step and then sum created, resume and reopened.

    Create this as column

    timediff = datediff(table[Time],minx(filter(table,table[Ticket Id] =earlier(table[Ticket Id] && table[Time] >earlier(table[Time])),table[Time]),day)

     

    You can another column or measure an filter required steps

    Appreciate your Kudos.

    • Matic20's avatar
      Matic20
      Frequent Visitor
      6 years ago

      Still getting an error uisng earlier and including &&

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        6 years ago

        Hi,

        I am not sure whom you are replying to but have you tried my solution?  I even share my solution file with you.

    • Matic20's avatar
      Matic20
      Frequent Visitor
      6 years ago

      So far this what  i have 

      Earliest_Closed_Created =
      DATEDIFF(CALCULATE(MIN('Reports BI'[Time]),'Reports BI'[State] = "created"),CALCULATE(MIN('Reports BI'[Time]),'Reports BI'[State] = "closed"),MINUTE)
       
      Resumed_Paused = DATEDIFF(CALCULATE(MIN('Reports BI'[Time]),'Reports BI'[State] = "paused"),CALCULATE(MIN('Reports BI'[Time]),'Reports BI'[State] = "resumed"),MINUTE)
       
      LastClosed_Reopened = IF(
      ISBLANK(
      CALCULATE(
      MIN('Reports BI'[Time]),'Reports BI'[State]="reopened")),
      DATEDIFF(
      CALCULATE(MIN('Reports BI'[Time]),'Reports BI'[State]="reopened"),
      CALCULATE(MAX('Reports BI'[Time]),'Reports BI'[State]="closed"),MINUTE))
       
      LastClosed_Reopened is returnining on;y blanks 
      Earliest_Closed_Created and LastClosed_Reopened are so far returning the correct information