Nicely asked question! Here's the calculation you are looking for:
IF SUM([Obs Failed]) = 0 THEN 'PASSED'
ELSEIF FIRST()=0 THEN 'NEW'
ELSEIF LOOKUP(SUM([Obs Failed]), -1) = 0 THEN 'NEW'
ELSE 'EXISTING' END
It needs to be set to Advanced, Restarting every KId, like this:
See attached 9.2 workbook.
KDI Status.twbx 21.8 KB
Thank you for your solution. Work exactly as desired. Kudos