Status & Workflow Processing Time

[lukeydb_]

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

Workflow	Promo Start	Promo end	Status	Cost	Workflow Change Step
Workflow 1	01/03/2023	31/12/2023	Step 1	50,000.00	28/02/2023
Workflow 1	01/03/2023	31/12/2023	Step 2	50,000.00	01/03/2023
Workflow 1	01/03/2023	31/12/2023	Step 3	50,000.00	02/03/2023
Workflow 1	01/03/2023	31/12/2023	Step 4	50,000.00	25/04/2023
Workflow 1	01/03/2023	31/12/2023	Step 5	50,000.00	25/04/2023
Workflow 1	01/03/2023	31/12/2023	Step 6	75,000.00	26/04/2023
Workflow 1	01/03/2023	31/12/2023	Step 7	75,000.00	15/05/2023
Workflow 1	01/03/2023	31/12/2023	Step 8	75,000.00	22/05/2023
Workflow 2	01/03/2023	31/12/2023	Step 1	25,000.00	25/04/2023
Workflow 2	01/03/2023	31/12/2023	Step 2	50,000.00	26/04/2023
Workflow 3	01/01/2023	31/12/2023	Step 1	90,000.00	25/04/2023
Workflow 3	01/01/2023	31/12/2023	Step 2	90,000.00	25/04/2023
Workflow 3	01/01/2023	31/12/2023	Step 3	120,000.00	26/04/2023
Workflow 3	01/01/2023	31/12/2023	Step 4	120,000.00	15/05/2023
Workflow 3	01/01/2023	31/12/2023	Step 5	120,000.00	22/05/2023
Workflow 3	01/01/2023	31/12/2023	Step 6	120,000.00	13/10/2023
Workflow 3	01/01/2023	31/12/2023	Step 7	120,000.00	13/10/2023
Workflow 3	01/01/2023	31/12/2023	Step 8	120,000.00	16/10/2023
Workflow 3	01/01/2023	31/12/2023	Step 9	120,000.00	18/10/2023
Workflow 3	01/01/2023	31/12/2023	Step 10	150,000.00	03/11/2023
Workflow 3	01/01/2023	31/12/2023	Step 11	150,000.00	06/11/2023
Workflow 3	01/01/2023	31/12/2023	Step 12	150,000.00	07/11/2023
Workflow 3	01/01/2023	31/12/2023	Step 13	150,000.00	09/11/2023

[saurabhtd]

@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 · Accepted solution]

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.