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,

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,

Mike22's avatar
Mike22
Helper III
7 years ago

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

    Hey Dale,


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


    Best Regards,

    Mike