Flag account if something has happened for 3 consecutive months

[Syk]

Hi everyone! I'm trying to identify when an account is uninsured for >= 3 months in a row.  My data (example in screenshot) will count each day that an account is uninsured. If the count is >1 it should show as uninsured for the whole month.

I need a way to flag the accounts that have been uninsured for >=3 months reguardless of when it happened. In the screenshot below, I'd need to flag accounts 456 and 789 but NOT 123. I hope this makes sense, please let me know if you have additional questions.

[VahidDM · Accepted solution]

Hi @Syk 

Try this measure to find the Accounts with 3 month uninsured 1 in line:

Flag = 
Var _A =ADDCOLUMNS('Table',"3M",CALCULATE(sum('Table'[uninsured]),filter(ALLEXCEPT('Table','Table'[account]),'Table'[date] in DATESINPERIOD('Table'[date],EARLIER('Table'[date]),-3,MONTH))))
Var _B = filter(_A,[3M]=3)
return
COUNTROWS(_B)

then you can use that in the conditional formatting of your visual.

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/