Rolling Window Conditional Checks

Hi Anonymous,

I have a solution for you, but I must challenge your logic.

FSC247 for Bill and FSC247 + FSC313 for Melinda should be considered valid not because they have 2 valid checks in the previous 365 days but because they happened within one year after the training.

So, on the first step we calculate this difference:

On the second step we create the Validity column:

First step CC in plain text:

Days since training = 
VAR _curPerson = [Person]
VAR _trainingDate = MINX ( FILTER ( People, [Person] = _curPerson ), [Food Safety Class Date] )
VAR _curDate = [Food Safety Check]
RETURN DATEDIFF ( _trainingDate, _curDate, DAY )

Second step CC in plain text:

Validity = 
VAR Trigger1 = IF ( [Days since training] <= 365, TRUE (), FALSE () )
VAR CurrentPerson = [Person]
VAR CurrentDate = [Food Safety Check]
VAR PrevYrDate = [Food Safety Check] - 365
VAR _tbl1 = FILTER ( Checks, [Days since training] > 365 && [Food Safety Check] <= CurrentDate && [Person] = CurrentPerson )
VAR _tbl2 = ADDCOLUMNS ( _tbl1, 
                         "Trigger",
                         VAR CurDate = [Food Safety Check]
                         VAR CurDate_1y = CurDate - 365   
                         RETURN COUNTX ( FILTER ( Checks, [Person] = CurrentPerson && [Food Safety Check] < CurDate && [Food Safety Check] >= CurDate_1y ), [FSC ID] ) )
RETURN IF ( Trigger1, "Valid", IF ( COUNTX ( FILTER ( _tbl2, [Trigger] < 2 ), [FSC ID] ) > 0, "Invalid", "Valid" ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian