Forum Discussion

lukeydb_'s avatar
lukeydb_
New Member
2 years ago
Solved

Status & Workflow Processing Time

I have a data set of workflow approval, showing an approval step by certain individuals, and wanting to calculate the total time for approval of a workflow and the total times in which the workflow is held within a status:

 

Things I want to calculate:

- Workflow time spent in each status

- Value change within each status step change

 

WorkflowPromo StartPromo endStatusCostWorkflow Change Step
Workflow 101/03/202331/12/2023Step 1    50,000.0028/02/2023
Workflow 101/03/202331/12/2023Step 2    50,000.0001/03/2023
Workflow 101/03/202331/12/2023Step 3    50,000.0002/03/2023
Workflow 101/03/202331/12/2023Step 4    50,000.0025/04/2023
Workflow 101/03/202331/12/2023Step 5    50,000.0025/04/2023
Workflow 101/03/202331/12/2023Step 6    75,000.0026/04/2023
Workflow 101/03/202331/12/2023Step 7    75,000.0015/05/2023
Workflow 101/03/202331/12/2023Step 8    75,000.0022/05/2023
Workflow 201/03/202331/12/2023Step 1    25,000.0025/04/2023
Workflow 201/03/202331/12/2023Step 2    50,000.0026/04/2023
Workflow 301/01/202331/12/2023Step 1    90,000.0025/04/2023
Workflow 301/01/202331/12/2023Step 2    90,000.0025/04/2023
Workflow 301/01/202331/12/2023Step 3  120,000.0026/04/2023
Workflow 301/01/202331/12/2023Step 4  120,000.0015/05/2023
Workflow 301/01/202331/12/2023Step 5  120,000.0022/05/2023
Workflow 301/01/202331/12/2023Step 6  120,000.0013/10/2023
Workflow 301/01/202331/12/2023Step 7  120,000.0013/10/2023
Workflow 301/01/202331/12/2023Step 8  120,000.0016/10/2023
Workflow 301/01/202331/12/2023Step 9  120,000.0018/10/2023
Workflow 301/01/202331/12/2023Step 10  150,000.0003/11/2023
Workflow 301/01/202331/12/2023Step 11  150,000.0006/11/2023
Workflow 301/01/202331/12/2023Step 12  150,000.0007/11/2023
Workflow 301/01/202331/12/2023Step 13  150,000.0009/11/2023
processing
status
workflow
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi lukeydb_ 

     

    Maybe you can try these method:

    I used your data to try:

     

    Then created a measure:

    Date_sum =
VAR _Workflow = SELECTEDVALUE('Table (2)'[Workflow])
// Get the current workflow
VAR _FIl = FILTER(
            ALLSELECTED('Table (2)'),
            'Table (2)'[Workflow]=_Workflow
            )
// Filter by Workflow
VAR _maxDate = CALCULATE(
                    MAX('Table (2)'[Workflow Change Step]),
                    _FIl
                    )
VAR _minDate = CALCULATE(
                    MIN('Table (2)'[Workflow Change Step]),
                    _FIl
                    )
// Get the date maximum and minimum values for each workflow
RETURN DATEDIFF(_minDate,_maxDate,DAY)
    DIFF_WORKFLOW =
VAR _Workflow = SELECTEDVALUE('Table (2)'[Workflow])
VAR _status = MID(MAX('Table (2)'[Status]),6,2)
//get the current status number
VAR _currentdate = MAX('Table (2)'[Workflow Change Step])
//get the current Workflow Change Step
VAR _Perviousdate = CALCULATE(
                        MAX('Table (2)'[Workflow Change Step]),
                            FILTER(
                                ALLSELECTED('Table (2)'),
                                MID('Table (2)'[Status],6,2)<_status
                                &&
                                'Table (2)'[Workflow]=_Workflow
                                )
                            )
//get the last date value
RETURN IF(
        ISBLANK(_Perviousdate)=FALSE(),
        DATEDIFF(_Perviousdate,_currentdate,DAY)
        )

    click the show Items with no data

    The result is as follow:

    Best Regards,

    Zhengdong Xu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • saurabhtd's avatar
    saurabhtd
    Resolver II
    2 years ago

    lukeydb_ You can try these measures 

     

    Workflow Time Spent in Each Status: = 
VAR CurrentStatus = SELECTEDVALUE(WorkflowData[Status])
VAR CurrentWorkflow = SELECTEDVALUE(WorkflowData[Workflow])
RETURN
    CALCULATE(
        SUMX(
            FILTER(
                WorkflowData,
                WorkflowData[Workflow] = CurrentWorkflow
                && WorkflowData[Status] = CurrentStatus
            ),
            DATEDIFF(
                WorkflowData[Workflow Change Step],
                EARLIER(WorkflowData[Workflow Change Step]),
                DAY
            )
        )
    )
    Value Change Within Each Status Step Change = 
VAR CurrentStatus = SELECTEDVALUE(WorkflowData[Status])
VAR CurrentWorkflow = SELECTEDVALUE(WorkflowData[Workflow])
RETURN
    CALCULATE(
        SUMX(
            FILTER(
                WorkflowData,
                WorkflowData[Workflow] = CurrentWorkflow
                && WorkflowData[Status] = CurrentStatus
            ),
            WorkflowData[Cost] - EARLIER(WorkflowData[Cost])
        )
    )
    Total Approval Time for a Workflow = 
VAR CurrentWorkflow = SELECTEDVALUE(WorkflowData[Workflow])
RETURN
    CALCULATE(
        MAX(WorkflowData[Workflow Change Step]) - MIN(WorkflowData[Workflow Change Step]),
        FILTER(WorkflowData, WorkflowData[Workflow] = CurrentWorkflow)
    )

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi lukeydb_ 

     

    Maybe you can try these method:

    I used your data to try:

     

    Then created a measure:

    Date_sum =
VAR _Workflow = SELECTEDVALUE('Table (2)'[Workflow])
// Get the current workflow
VAR _FIl = FILTER(
            ALLSELECTED('Table (2)'),
            'Table (2)'[Workflow]=_Workflow
            )
// Filter by Workflow
VAR _maxDate = CALCULATE(
                    MAX('Table (2)'[Workflow Change Step]),
                    _FIl
                    )
VAR _minDate = CALCULATE(
                    MIN('Table (2)'[Workflow Change Step]),
                    _FIl
                    )
// Get the date maximum and minimum values for each workflow
RETURN DATEDIFF(_minDate,_maxDate,DAY)
    DIFF_WORKFLOW =
VAR _Workflow = SELECTEDVALUE('Table (2)'[Workflow])
VAR _status = MID(MAX('Table (2)'[Status]),6,2)
//get the current status number
VAR _currentdate = MAX('Table (2)'[Workflow Change Step])
//get the current Workflow Change Step
VAR _Perviousdate = CALCULATE(
                        MAX('Table (2)'[Workflow Change Step]),
                            FILTER(
                                ALLSELECTED('Table (2)'),
                                MID('Table (2)'[Status],6,2)<_status
                                &&
                                'Table (2)'[Workflow]=_Workflow
                                )
                            )
//get the last date value
RETURN IF(
        ISBLANK(_Perviousdate)=FALSE(),
        DATEDIFF(_Perviousdate,_currentdate,DAY)
        )

    click the show Items with no data

    The result is as follow:

    Best Regards,

    Zhengdong Xu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.