Forum Discussion
Rolling Window Conditional Checks
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?
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
1 Reply
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