Rolling sum of counts for 5 days interval with suppression period

[raghaven97]

I'm trying to get the details (send alert) of Machine_ID for which the count of error_code is more than 2 in last 7 days. This should be applicable for entire 2022 year data because we have to suppress the machine for 45 days when the alert has been sent. The machine will be considered only after 45 days.

Dataset

The required output is as below.

Output

[Ashish_Mathur]

Hi,

Not clear of how you arrived at the Count and Trigger columns.

[raghaven97]

By using Group By for the Error_code by Machine_ID and Date I will get the Count column. I need to create a cloumn Trigger where it will tell me when to send an alert. This shall be calculated based on below scenario:

If the Error_code count is more than 2 in last 7 days and there is no Error_code in last 45 days then it shall be 1 else 0.

[Ashish_Mathur]

It is still not clear to me.  Is it possible to show your calculations in an MS Excel file.  I can understand those formulas and convert them to calculated columns formulas in the DAX language.

[raghaven97]

In the above image you can see that there are 3 errors for ID 10 between 1/1/2022 aand 1/2/2022. So I have to create a column called trigger/alert and place 1 against 1/2/2022 and alert will be sent. The ID will be suppressed for next 45 days i.e. 2/16/2022. 

Again between 4/2/2022 and 4/4/2022 we have 3 errors so I need to place 1 against 4/4/2022 and alert will be sent, The ID will be suppressed again for next 45 days. This should happen for each ID. 
I do not have formulas in Excel, this was calculated manually.

Apologies if I am not making things clear here.  

[Ashish_Mathur]

Hi,

45 days back from 4/4/2022 is 2/18/2022.  So between these 2 dates, there was 2 alarms on 4/2/2022 and 4/3/2022.  Therefore, since your second condition is not satisfied, there should not be a 1 against that date.  Please clarify.

[raghaven97]

Yes, that's correct but we have to consider 45 days suppression from 1/2/2022 because that is our first alarm. So we have suppress the machine till 2/16/2022. There are 3 errors between 2/3/2022 and 2/4/2022 but we should not trigger alarm since it is under 45 days suppression period from the previous alarm.

I will have to consider the machine only after 45 days even there is an alarm.