datetime
1 Topic
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?Solved722Views1like1Comment