Rolling Window Conditional Checks

[calcstuff]

I've got Bill and Melinda who've taken food safety classes:

Person	Food Safety Class Date
Bill	3/18/2020
Melinda	4/15/2020

Once you've taken a food safety class you're qualified to perform food safety checks (FSC) for the next year. After that first 365 days is up you have to continuously perform at least 2 food safety checks for the previous 1 year period to maintain your food safety certification, and be able to perform more food safety checks. Here's the log of food safety checks:

Person	Food Safety Check	FSC ID
Bill	5/18/2020	FSC005
Bill	5/30/2020	FSC154
Bill	1/14/2021	FSC247
Bill	2/25/2023	FSC313
Melinda	6/15/2020	FSC005
Melinda	7/14/2020	FSC154
Melinda	2/28/2021	FSC247
Melinda	3/15/2021	FSC313
Melinda	3/29/2022	FSC399
Melinda	3/30/2022	FSC502

In Power BI DAX how can I determine which food safety checks were valid and which were not? The expected output would look like this:

Person	Food Safety Check	FSC ID	# FSC Past Year	Validity	Notes
Bill	5/18/2020	FSC005	N/A	Valid	Grace period, as initial class was within the first year
Bill	5/30/2020	FSC154	N/A	Valid	Grace period, as initial class was within the first year
Bill	1/14/2021	FSC247	2	Valid	None
Bill	2/25/2023	FSC313	0	Invalid	None
Melinda	6/15/2020	FSC005	N/A	Valid	Grace period, as initial class was within the first year
Melinda	7/14/2020	FSC154	N/A	Valid	Grace period, as initial class was within the first year
Melinda	2/28/2021	FSC247	2	Valid	None
Melinda	3/15/2021	FSC313	2	Valid	None
Melinda	3/10/2022	FSC399	1	Invalid	None
Melinda	3/11/2022	FSC400	2	Invalid	Once you're invalid everything after is invalid
Melinda	3/12/2022	FSC401	3	Invalid	Once you're invalid everything after is invalid

To use Bill as an example:

• Bill passed his food safety class on 3/18/2020
• After that Bill can perform food safety checks for 1 year, he performed two (FSC005, FSC154) and they are valid.
• Next Bill performed food safety check FSC247 and he did indeed perform at least 2 food safety checks in the previous 365 days, so this FSC247 is valid
• On 2/25/2023 Bill performed food safety check FSC313 and this is invalid because he had 0 food safety checks in the previous 365 days
• The last point in time Bill had at least 2 audits within the previous 365 days while still being qualified was 5/30/2021. Anything after 5/30/2021 is therefore invalid.

I manually created and calculated the "# FSC Past Year" and "Validity" columns. How can I create them with DAX?

[barritown · Accepted solution]

Hi @calcstuff,

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