Forum Discussion
Counting Status changes across multiple Rows utilizing date fields
Hello,
I have any interesting count I'm trying to run and I'm having some issues trying to get the right answer. To lay the ground work and what I'm looking to accomplish, here is what I have. I have the following columns Record Id, Workflow Stage, Workflow Tracking Type (action executed value), Workflow Date Modified (tracks when a workflows action was executed), Date Created, and Investigation due date. The workflow stages have the following values; Draft, Verification, Investigation, Approval, and NULL for completed. When a workflow stage is transitioned to the next workflow stage, we capture the date this happens (Workflow Date Modified). Unfortunatley we don't have a column to capture when a workflow stage is due, otherwise I wouldn't be having an issue counting what I'm trying to count.
Here is what I'm trying to count, I want to count any record that was overdue and then completed. The due dates for each workflow stage are as follows.
Draft = 1 day since the incident was created and saved.
Verification = 1 day plus the date the workflow was moved into this stage. Essentially 1 day plus the "Draft" workflow stages "Workflow Date Modified" date.
Investigation = This is a value in the Investigation due date column, easy enough to calculate.
Approval = 5 days plus the date the workflow was moved into this stage. 5 days plus the Investigation workflow stages "Workflow Date Modified" date.
Each of these values listed above are all on different rows, but we have a record id number to tie them all together.
Here is an example dataset:
| Record ID | Workflow Stage | Workflow Tracking Type | Workflow Date Modified | Date Created | Investigation Due Date |
| 12345 | Draft | Action Excuted | 1/1/2023 | 1/1/2023 | 1/6/2023 |
| 12345 | Verification | Action Executed | 1/1/2023 | 1/1/2023 | 1/6/2023 |
| 12345 | Investigation | Action Executed | 3/29/2023 | 1/1/2023 | 1/6/2023 |
| 12345 | Approval | Action Executed | 3/30/2023 | 1/1/2023 | 1/6/2023 |
I'm trying to figure out how to calculate if one of these workflow stages is overdue, but has been closed out, then count that workflow stage. Eventually ending up with a count of "rows" where the Workflow stage was completed past it's theoretical due date. In the above scenario only the Investigation stage would be considered overdue.
Thank You,
Trevor Bensen
you can try to createa a step column
step =SWITCH('Table'[Workflow Stage],"Draft",1,"Verification",2,"Investigation",3,"Approval",4)then create the overdue columnColumn =var _draft=maxx(FILTER('Table','Table'[Record ID]=EARLIER('Table'[Record ID])&&'Table'[step]=1),'Table'[Workflow Date Modified])var _inve=maxx(FILTER('Table','Table'[Record ID]=EARLIER('Table'[Record ID])&&'Table'[step]=3),'Table'[Workflow Date Modified])return if('Table'[step]=1 &&'Table'[Workflow Date Modified]>_draft+1,1,if('Table'[step]=3 &&'Table'[Workflow Date Modified]>'Table'[Investigation Due Date],1,if('Table'[step]=4&&'Table'[Workflow Date Modified]>_inve+5,1,0)))pls see the attachment below
8 Replies
you can try to createa a step column
step =SWITCH('Table'[Workflow Stage],"Draft",1,"Verification",2,"Investigation",3,"Approval",4)then create the overdue columnColumn =var _draft=maxx(FILTER('Table','Table'[Record ID]=EARLIER('Table'[Record ID])&&'Table'[step]=1),'Table'[Workflow Date Modified])var _inve=maxx(FILTER('Table','Table'[Record ID]=EARLIER('Table'[Record ID])&&'Table'[step]=3),'Table'[Workflow Date Modified])return if('Table'[step]=1 &&'Table'[Workflow Date Modified]>_draft+1,1,if('Table'[step]=3 &&'Table'[Workflow Date Modified]>'Table'[Investigation Due Date],1,if('Table'[step]=4&&'Table'[Workflow Date Modified]>_inve+5,1,0)))pls see the attachment belowHello,
I think this might work! One thing I am seeing is that it appears to only be showing an Investigation or Approval workflow stage as being overdue. Is there a way to apply this to the Verification stage which has a due date of +1 after the Draft stage has been completed?
Would I add another If statement into the Return formula to to look at "Step=2" and WorkflowDate Modified > _draft+2?
Thanks,
Trevor Bensen
Hi TBensen
Thanks for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank GeraldGEmerick and ryan_mayu , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you
Best Regards,
Community Support TeamTBensen What is the expected output given the input data? You will likely want to unpivot your date columns but tough to know for certain as it is difficult to follow what you are trying to accomplish.
Hello,
Sorry, I should have included that in the original post. I'll go back and add it. I want to count every instance where a Workflow stage was completed past its theoretical due date. In the example above, the Investigatoin workflow stage is past its due date, so that would be counted as 1. The Approval workflow stage wouldn't be past due as it had been completed within 1 day of moving into the approval workflow stage.
Thanks,
Trevor Bensen
