DAX for measuring Pending to Close and Active ?

[Chris_Price]

Hi Everyone - am new to Powerbi and looking for an URGENT help please.

My sample data is as follows;

 

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-janeyg-msft]

Hi, @Chris_Price 

It’s my pleasure to answer for you.

According to your description, I think you can create a measure to calculate the correct result of the first question.

Like this:

closednumber =
VAR d =
    SUMMARIZE (
        Table1,
        Table1[Work item no],
        "flag",
            VAR a =
                MINX (
                    FILTER (
                        ALL ( Table1 ),
                        [Work item no] = EARLIER ( Table1[Work item no] )
                            && [Status] = "Closed"
                    ),
                    [Date_time]
                )
            VAR b =
                MINX (
                    FILTER (
                        ALL ( Table1 ),
                        [Work item no] = EARLIER ( Table1[Work item no] )
                            && [Date_time] < a
                    ),
                    [Date_time]
                )
            VAR c =
                DATEDIFF ( b, a, HOUR )
            RETURN
                IF ( c <= 24, 1 )
    )
RETURN
    COUNTROWS ( FILTER ( d, [flag] = 1 ) )

I’m sorry that it is difficult to understand your logic of the three questions, could you please give me further explainations and the form of desired results.

Best Regards

Janey Guo

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

[Chris_Price]

@v-janeyg-msft 
I want to create charts basis 3 requirements wherein count of such items where;

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-janeyg-msft]

Hi, @Chris_Price 

I'm sorry that what you said is too simple and your data is not representative.It is not clear about the initial status of an item ‘pending’ or ‘active’?If an item is repeatedly opened and closed several times, calculate one or more?calculate first or total?

Please give me some simple data that meets the conditions and complete your requirements,so that we can help you.

Best Regards

Janey Guo

[Chris_Price]

@v-janeyg-msft

Here is again a sample data;

Work item no. State. Date_time

Abc. Pending. 03-24-20; 23:30:20

Abc. Active. 03-30-20; 20:38:19

Bks. Active. 02-04-20; 18:34:09

Bks. Closed. 06-19-20; 17:30:12

Abc. Closed. 20-08-20; 11:20:35

Abc Active. 30-9-20; 16:45:07

My requirement is simply to understand :-

1) count of work items which got "closed" within 24 hrs ? Once and if they got re-opened we want to calculate those cases in Q.3 below!

2) count of work items which moved from "Pending" to "active" status ? This is only one way - Pending to Actve and not vice versa

3) count of work items which got re-opened. This is straightforward those cases which got re opened after being "Closed"!

[lbendlin]

Please provide the sample data in usable form. A screenshot does not qualify as "usable".

[Chris_Price]

Here it is;

Work item no.     Status.      Date_time

Abc.                     Pending.   24-03-20; 23:30:20

Abc.                     Active.       30-03-20; 20:38:19

Bks.                      Active.      02-04-20; 18:34:09

Bks.                      Closed.     19-06-20; 17:30:12

Abc.                     Closed.      20-08-20; 11:20:35

Abc                      Active.       30-9-20;    16:45:07

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)?