Counting Status changes across multiple Rows utilizing date fields

[TBensen]

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

[v-karpurapud]

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 Team  

[ryan_mayu · Accepted solution]

@TBensen 

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 column

Column =

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

[TBensen]

Hello,

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

[ryan_mayu]

@TBensen 

yes , the your coding logic is correct. And you can add one more if condition in the DAX.

[TBensen]

Excellent, thank you for your help!

[ryan_mayu]

you are welcome

[TBensen]

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

[GeraldGEmerick]

@TBensen 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.