Forum Discussion

Mike22's avatar
Mike22
Helper III
7 years ago
Solved

Date difference between workflow actions on a table

  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 ...
Tips and Tricks
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

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

    Date-difference-between-workflow-actions-on-a-table2

     

    Best Regards,

Mike22's avatar
Mike22
Helper III
7 years ago

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's avatar
Mike22
Helper III
7 years ago

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

    • Mike22's avatar
      Mike22
      Helper III
      7 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        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,