Difference btn dates with many dependences

[Matic20]

Time	Ticket Id	Agent	State
1/1/2020 13:48	26625	Chris  Herbert	created
1/2/2020 1:13	26625	Chris  Herbert	paused
1/2/2020 14:23	26625	Chris  Herbert	resumed
1/2/2020 14:25	26625	Chris  Herbert	closed
1/2/2020 14:30	26625	Chris  Herbert	reopened
1/2/2020 14:32	26625	Chris  Herbert	closed
1/1/2020 14:22	26626	Chris  Herbert	created
1/2/2020 1:13	26626	Chris  Herbert	paused
1/2/2020 14:23	26626	Chris  Herbert	resumed
1/2/2020 14:25	26626	Chris  Herbert	closed
1/2/2020 5:39	26628	Chris  Herbert	created
1/2/2020 5:41	26628	Chris  Herbert	paused
1/3/2020 3:49	26628	Chris  Herbert	resumed
1/3/2020 3:55	26628	Chris  Herbert	closed
1/2/2020 6:08	26629	Omar  Salama	created
1/2/2020 6:26	26629	Omar  Salama	paused
1/2/2020 7:06	26629	Omar  Salama	resumed
1/2/2020 7:07	26629	Omar  Salama	closed
1/2/2020 9:07	26634	Anmar  Raouf	created
1/2/2020 9:09	26634	Anmar  Raouf	paused
1/2/2020 9:47	26634	Anmar  Raouf	resumed
1/6/2020 8:43	26634	Anmar  Raouf	closed

Expected Result(Minutes)
Ticket ID	Earliest Closed-Created_Date	Resumed- Paused	Last Closed-Reopened	Achieved SLA	Agent
26625	1447	755	2	694	Chris  Herbert
26626	1443	790	0	653	Chris  Herbert
26628	1336	1328	0	8	Chris  Herbert
26629	59	40	0	19	Omar  Salama
26634	5736	38	0	5698	Anmar  Raouf

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

[Zas1442]

[Zas1442]

 hi

[Zas1442]

www.sonic30.com.Au

[Greg_Deckler]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Matic20]

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 

 

[amitchandak]

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]

Still getting an error uisng earlier and including &&

[Matic20]

Hello Ashish Mathur

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

Thank you 

[Matic20]

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 

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

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

[Ashish_Mathur]

Hi,

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