topic Status & Workflow Processing Time in DAX Commands and Tips

Status & Workflow Processing Time

lukeydb_ — Thu, 18 Jan 2024 16:29:20 GMT

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

Re: Status & Workflow Processing Time

saurabhtd — Fri, 19 Jan 2024 04:58:21 GMT

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

Re: Status & Workflow Processing Time

Anonymous — Fri, 19 Jan 2024 09:10:16 GMT

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.