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 i...
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.

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