Forum Discussion
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
| 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 |
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
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.
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) )
