cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Matic20
Frequent Visitor

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)

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

SLA.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
Zas1442
Regular Visitor

image.jpg

 hi

Greg_Deckler
Super User
Super User

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

SLA.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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 
 
 
 
Power bi.PNG
 
 
 
amitchandak
Super User
Super User

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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Still getting an error uisng earlier and including &&

 

Matic20
Frequent Visitor

Hello Ashish Mathur

 

Am trying your solution by the Last_Closed - Reopened Column is returning blanks only 

 

Thank you 

Matic20
Frequent Visitor

Hello Ashish Mathur

 

Last closed-reopened = IF(ISBLANK(CALCULATE(MIN('Reports BI'[Time]),'Reports BI'[State]="reopened")),0,1440*(CALCULATE(Max('Reports BI'[Time]),'Reports BI'[State]="Closed")-CALCULATE(MIN('Reports BI'[Time]),'Reports BI'[State]="reopened")))  // This worked and provided me with solution 
 
LastClosed_Reopened = IF(SBLANK(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)) // This is giving me blanks
 
I cant figure out the difference between 
 
And thank you, your solution provided me with the answer 
 

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

I can't say why.  It is working fine in the file i shared with you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!