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