Filtering a column based on criteria based on another column filtered by date

[Anonymous]

I have a table that contains different columns. These are the ones I need to use for what I want:

• PropertyStatus
• MoveInDate
• MoveOutDate
• CaseNumber.

Scenario:

If a property/CaseNumber has data under MoveInDate, it does not have any data for MoveOutDate, and viceversa.

PropertyStatus can have the following values: Complete, In Process & Not Started (among others I do not need to use for this example)

Every Property has to be under the status of "Complete" 15 business days before the MoveInDate/ MoveOutDate.

I want to have two columns that says: "Delayed" if this condition is not met.

One column will be named MoveInDelayedCaseNumber & another one named: MoveOutDelayedCaseNumber

Pseudocode for MoveInDelayedCaseNumber:

If (PropertyStatus = 'Not Started' OR PropertyStatus = 'In Process') AND MoveInDate is less than 15 business days from Today(), Delayed, (otherwise) OnTime

Pseudocode for MoveOutDelayedCaseNumber:

If (PropertyStatus = 'Not Started' OR PropertyStatus = 'In Process') AND MoveOutDate is less than 15 business days from Today(), Delayed, (otherwise) OnTime

How can I do this? Please feel free to propose another approach if you think it will be a better option.

[Anonymous]

It worked. Thank you!

[Whitewater100 · Accepted solution]

Hi:

It looks like you are really close. 

I wonder if you can create a calculated column with a couple of variables

MOVE IN Delayed = 
var  benchmarkdate = TODAY() +15
return
IF(Table[PropertyStatus] = "Not Started" || Table[PropertyStatus] = "In Process" && Table[MoveInDate] < benchmarkdate, "Delayed", "On Time"))

Could you try this one and see if it works for you? The next one will be very similiar - just sub in MoveOutDate for the MOVEIN Date.

I hope this helps..