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

     

GeraldGEmerick's avatar
GeraldGEmerick
Icon for Memorable Member rankMemorable Member
11 months ago

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.