I am trying to get the latest test number and date of the latest Non-Compliant (NC) test where there has not been another Test of the same test number completed after that NC test. I create 3 calculated columns to store this information.
Last_NC_Test_Number - last NC test number
Last NC DATE - date of the last NC test
Has Outstanding NC - flag if a follow-up test is required if the follow up has been completed with past 30 days.
Examples:
An employee had a NC test 123 on 01/01/2023 and has not had another test 123 since 01/01/2023.
- Last_NC_Test_Number = 123
- Last NC DATE - 01/01/2023
- Has Outstanding NC - Y
An employee had a NC test 123 on 01/01/2023 and has had another test 123 on 12/01/2023.
- Last_NC_Test_Number = 123
- Last NC DATE - 01/01/2023
- Has Outstanding NC - N
An employee had a NC test 345 and NC 123 both on 01/01/2023 and has had another test 345 on 02/01/2023.
- Last_NC_Test_Number_Contact_Column = 123
- Last NC DATE - 01/01/2023
- Has Outstanding NC - Y
I have the following formula but it is not retruning what I need because of MAX in calculate:
VAR LastNC_Date = CALCULATE(max(Evaluation[Date__c]),Evaluation[EmployeeId__c] && Evaluation[OTI_ResultForFiltering] = "NC" && Evaluation[TestNumber__c] <> "NC")
RETURN
CALCULATE(max(Evaluation[TestNumber__c]), Evaluation[OTI_ResultForFiltering] = "NC" , Evaluation[Date__c] = LastNC_Date)
