DAX to calculate Statuses Journey

[Chris_Price]

Hi Everyone - am new to Powerbi and looking for an URGENT help please. My sample data is as follows;

Workitem No.	Status	Date_Time
Sep-20 3450912	Active	4/7/20 13:10
Sep-20 3450912	Pending	14/8/20 20:40:15
2309185934	Pending	6/5/2020_10:20:06
2309185934	Active	20/8/2020_23:18:24
2309185934	Closed	25/8/2020_12:27:02
IUS123049512	Active	5/3/2020_14:10:25
IKO093845751	Closed	15/10/2020_17:27:18
120349567	Pending	18/12/2019_19:24:48
120349567	Active	26/2/2020_08:34:10
120349567	Closed	10/6/2020_20:34:18
120349567	Pending	20/7/2020_16:38:20

I need to calculate the following using above data;

1) How many workitems are 'closed' within 24 hrs SLA?

2) How many workitems are moved from 'Pending' to 'Active' status within 24 hrs?

3) How many workitems are re-opened (after closed status)?

@v-xuding-msft 

@amitchandak  @Greg_Deckler @Ashish_Mathur @Zubair_Muhammad

[v-lionel-msft]

Hi @Chris_Price ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Chris_Price]

 

@v-lionel-msft

unfortunately Q1 and Q3 are coming out weirdly like this;

It should show me team wise split when put in a table ?

[amitchandak]

@Chris_Price , Try formula's like

meet SLA= countx(filter(summarize(Table,Table[Workitem No],"_1", datediff(calculate(min(Table[Date_Time]), filter(Table, Table[Status]= "Pending"))
,calculate(min(Table[Date_Time]), filter(Table, Table[Status]= "Closed")), hour)
), [_1] <=24),[Workitem No])
moved Active= countx(filter(summarize(Table,Table[Workitem No],"_1", datediff(calculate(min(Table[Date_Time]), filter(Table, Table[Status]= "Pending"))
,calculate(min(Table[Date_Time]), filter(Table, Table[Status]= "Active")), hour)
), [_1] <=24),[Workitem No])
moved Open= countx(filter(summarize(Table,Table[Workitem No],"_1", datediff(calculate(Max(Table[Date_Time]), filter(Table, Table[Status]= "Closed"))
,calculate(max(Table[Date_Time]), filter(Table, Table[Status]= "Active")), hour)
), [_1] >0),[Workitem No])

[Chris_Price]

Proposed solution doesn't work @amitchandak the way I wanted to be. 
For e.g in condition 1- applied measure still shows statuses as Active, Pending and Closed both while I want to see only those cases which were Closed within 24 hrs SLA.