Date difference between workflow actions on a table

[Mike22]

Hello Community,

I need your help please. 

I have the following table tracking the workflow actions for each document ID keeping track of the sequence number, who is pending action and the date of action. I want to create measures to count the number of days it takes for each actor to take the action. The objective is to see the average and understand where the delay is.  

There are two different type of requests with the same ID once TR is completed ER will be done with the same ID. The two requests are independent so the date count of the sequence starts from Submitted for both not from Draft and has a different max value.

Another important note as if there is a weekend in the middle of the workflow dates it should not be counted. It may be possible though that action is taken in the weekend, in this case it would be as if it is done on a Friday so it would count as done in 1 day. When instead a request is submitted during the weekend the count will start from the Monday. I am using worst case scenario so if action is taken by two actors on the same day it will count as 1 if it is done on the next day as 2 and so on.

Below is the table:

ID	Request	Sequence number	status	Approved Date	Max Sequence	Pending action from
22	TR	1	Draft	29/05/2017	12	Staff
22	TR	2	Submitted	29/05/2017	12	CO
22	TR	3	TSA Returned	29/05/2017	12	Staff
22	TR	4	Draft	29/05/2017	12	Staff
22	TR	5	Submitted	29/05/2017	12	CO
22	TR	6	TSA Approved	05/06/2017	12	TPO
22	TR	7	TO Returned	06/06/2017	12	CO
22	TR	8	TSA Returned	06/06/2017	12	Staff
22	TR	9	Draft	06/06/2017	12	Staff
22	TR	10	Submitted	06/06/2017	12	CO
22	TR	11	TSA Approved	06/06/2017	12	TPO
22	TR	12	TO Approved	07/06/2017	12	Finish
22	ER	2	Submitted	03/07/2017	7	CO
22	ER	3	TSA Returned	13/07/2017	7	Staff
22	ER	4	Submitted	17/07/2017	7	CO
22	ER	5	TSA Approved	19/07/2017	7	TPO
22	ER	6	TO Approved	21/07/2017	7	TCU
22	ER	7	TCU Approved	27/07/2017	7	Finish
22	ER	1	Draft	03/07/2017	7	Staff

These are the measures:

Measure 1

Staff time on TR= 2 days (this is 2 because even though staff actioned several times it was always done in the same day)

Measure 2

CO Time in TR= 6 days (8-2 as there is a weekend in the middle. This is the difference between sequence 5 and 6 where the workflow was pending action from CO)

Measure 3

TPO time in TR= 2 days (sequence 6 and 7, multiple actions happen on the same day and are not counted.)

Measure 4

Total time to process TR = 9 days

I would then like to have 4 measures which are the same but apply to the ER.

In addition, there is Measure 5

TCU time to process ER= 5 days (TCU to finish) 7-2 because of weekend.  

With the measures I would like to obtain in the end the average time for each actor for all TR and ER IDs

The ID I showed you is one of the complex ones there are also more simple examples which just 4 sequences.

The complications:

-I am not sure how I can take the max sequence and take is as a value to sum the date diff between the various actions beginning from lowest sequence number of status "submitted".

-Not sure how not to count days when multiple actions happen by the same actor.

Thanks a lot in advance for your usual help!

Mike

[v-jiascu-msft]

Hi Mike,

1. Are these measures the expected results?

2. Why does the measure 1 count the days while the measure 2 calculates the interval?

3. A date table is needed in your scenario to provide all the continuous dates. 

Best Regards,

[Mike22]

Hi Dale,

Thanks a lot for your reply.

• Yes the measures are the expected results.
• They are actually counting the number of days the workflow is pending action with the user. In measure one “Staff” takes action in one day, then the workflow goes back to “staff” in a later stage of the sequence where action is taken again. In measure 2 instead the workflow is pending action from “CO” for many days until action is taken and now is pending from “TPO”. So we need to count these days as pending action from "CO". Hope this clarifies
• Thanks for the suggestions, do you have any idea on how I can implement this?

Thanks,

Mike

[Mike22]

ID	Request	Sequence number	status	Approved Date	Max Sequence	Pending action from	Next Sequence Step	Date Difference
22	TR	1	Draft	29/05/2017	12	Staff	2
22	TR	2	Submitted	29/05/2017	12	CO	3
22	TR	3	TSA Returned	29/05/2017	12	Staff	4
22	TR	4	Draft	29/05/2017	12	Staff	5
22	TR	5	Submitted	29/05/2017	12	CO	6
22	TR	6	TSA Approved	05/06/2017	12	TPO	7
22	TR	7	TO Returned	06/06/2017	12	CO	8
22	TR	8	TSA Returned	06/06/2017	12	Staff	9
22	TR	9	Draft	06/06/2017	12	Staff	10
22	TR	10	Submitted	06/06/2017	12	CO	11
22	TR	11	TSA Approved	06/06/2017	12	TPO	12
22	TR	12	TO Approved	07/06/2017	12	Finish	0
22	ER	2	Submitted	03/07/2017	7	CO	3
22	ER	3	TSA Returned	13/07/2017	7	Staff	4
22	ER	4	Submitted	17/07/2017	7	CO	5
22	ER	5	TSA Approved	19/07/2017	7	TPO	6
22	ER	6	TO Approved	21/07/2017	7	TCU	7
22	ER	7	TCU Approved	27/07/2017	7	Finish	8
22	ER	1	Draft	03/07/2017	7	Staff	2

Hi All,

I tried to move forward with this. I added a new column which indicates what is the next step. This is not necessary as it can be included in the final formula but just to give you an idea of what I am moving towards.

I would like now to populate the “date difference column” to tell me what is the number of days between the current step and the next step. I will then manually filter on the “pending action” column to understand how many days have passed. How can I do this?

I have to do the calculation considering the ID  (column 1) and Request type (column 2) as for each one it is a new sequence. When I arrive to the max sequence number I will just put a 0 as number of days so it does not impact the SUM.

 Basically, doing date diff comparing rows and basing myself on a calendar which does not count the weekends.

Anyone can help?

Thanks,

Mike

[v-jiascu-msft]

Hi Mike,

Please download the demo from the attachment and check if these logics are good. 

Best Regards,

[Mike22]

Hi Dale,

Thank you very much! This is a great step forward and it is almost there. The calendar feature is great and really saves a lot of time.

Just a few issues

-Staff might have actions which take more than one day to complete so I think it should use the same logic of CO and TPO.

-I have re-attached the model with a few entries which are not working correctly when you filter them out individually on the table I added to the report. For example ID: 119924 should show 4 days with CO, instead it shows 0. ID:  207177 should show 1 and instead it shows 0. The same issue is probably affecting the TPO calculation as well for the same ID: 119924.

I cannot attach the .pbix file to the post so I have put it here. https://ufile.io/bwshz

Any ideas?

Thanks,

Mike

[v-jiascu-msft]

Hi Mike,

The logic of CO and TPO are different. Which one should be applied to Staff?

Why is it 4 days with CO to ID 119924? Can you share the details about how to calculate?

Best Regards,

[Mike22]

Hi @v-jiascu-msft

Thanks for your reply. This is the data set for 119924

ID	Request	Sequence number	status	Approved Date	Max Sequence	Pending action from
119924	TR	4	TO Approved	27/05/2016	4	Finish
119924	TR	3	TSA Approved	11/05/2016	4	TPO
119924	TR	2	Submitted	06/05/2016	4	CO
119924	TR	1	Draft	05/05/2016	4	Staff

The item stays with CO from 6 of May to 11 of May and there is a weekend in the middle. So we should count 6,9,10 and 11 = 4 days.

TPO = 11,12,13,16,17,18,19,20,23,24,26,27 = 12 days

Staff= is only the 6th so in this case it is 1 day.

TPO and CO have the same logic and this should be applied to the Staff as well. As in some cases after CO the request can go back to Staff who might take a few days to resubmit to CO.

Hope I was clear and thanks in advance for any further help.

Mike

[v-jiascu-msft · Accepted solution]

Hi Mike,

Please download the demo from the attachment. Adding a new column could make it easy.

DaysCost =
VAR currentId = [ID]
VAR currentRequest = [Request]
VAR currentSeq = [Sequence number]
VAR currentActor = [Pending action from]
VAR nextDate =
    CALCULATE (
        MIN ( Table1[Approved Date] ),
        FILTER (
            Table1,
            Table1[Sequence number]
                = currentSeq + 1
                && [ID] = currentId
                && Table1[Request] = currentRequest
        )
    )
VAR nextActor =
    CALCULATE (
        MIN ( Table1[Pending action from] ),
        FILTER (
            Table1,
            Table1[Sequence number]
                = currentSeq + 1
                && [ID] = currentId
                && Table1[Request] = currentRequest
        )
    )
RETURN
    IF (
        [Pending action from] = nextActor
            && nextDate = [Approved Date],
        0,
        CALCULATE (
            SUM ( 'Calendar'[ifWorkday] ),
            FILTER ( 'Calendar', [Date] >= [Approved Date] && [Date] <= nextDate )
        )
    )

Best Regards,

[Mike22]

Hey Dale,

Thank you soo much this does it perfectly! You have been of great help!

Best Regards,

Mike