Forum Discussion
Date difference between workflow actions on a table
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,
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,
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
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
Hi Mike,
Please download the demo from the attachment and check if these logics are good.
Best Regards,
