Forum Discussion

TBensen's avatar
TBensen
Icon for Helper I rankHelper I
11 months ago
Solved

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.  ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    11 months ago

    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's avatar
TBensen
Icon for Helper I rankHelper I
11 months ago

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