Count records that meet a condition for sequential months

[Car47]

Hi all, I'm trying to identify rows in a given month that satisfy the same condition for 3 consecutive months. I've already created a new column in the table that calculates whether the conditino is met (1=yes, 0=no). What I ultimately want to end up with is a graph showing number of records that have met the condition in 3 consecutive months and I don't know how to structure it.  Example:

Device	Month	Condition met
ABC	Sep 2020	1
BCD	Sep 2020	0
CDE	Sep 2020	0
ABC	Oct 2020	1
BCD	Oct 2020	0
CDE	Oct 2020	1
ABC	Nov 2020	1
BCD	Nov 2020	0
CDE	Nov 2020	0

This would calculate for that the most recent month only one Device met the condition for 3 consecutive months:

Device	Month	Count
ABC	Nov 2020	1
BCD	Nov 2020	0
CDE	Nov 2020	0

Can anyone help me calculate this, the result of which I want to graph by month. TIA!!

[Ahmedx]

Is this what you are looking for?

https://1drv.ms/u/s!AiUZ0Ws7G26RiV5m5k2Aw-udogR3?e=UfaMSY

[Car47]

Ahmedx, thanks for the reply! This gets me almost there. It definitely let's me identify the number of devices that met the condition for 3 consecutive months in the most recent month (MaxDate). I would like to capture and graph that number(count) for each of the last x# months, not just the most recent. So I would want to calculate the number in 'flag fixed' in your example per month. Can you think of a way to do that? Thx.

[Ahmedx · Accepted solution]

you need to replace

MaxDate = CALCULATE(MAX('Table'[Month]),ALLEXCEPT('Table','Table'[Device]))

to

MaxDate = MAX('Table'[Month])