Consecutive SLA Misses over a period of time

[ssk_1984]

Hi,

i have to find the consecutive misses of sla ' not met ' > 3 times by the unique value Metric Final ID.

in the below sample data, id 12 misses 3 consecutive time from 10th, 11th and 12th month of 2021 , that is count as 1...

then 2nd time 7th,8th & 9th month of 2022, counted as 2

then 3rd time it is from 8th, 9th & 10th month...Count as 3

sample data

Final ID	SLA Result	Report Date	Count	3 Consecutive Misses
12	Not Met	01-10-2021	1
12	Not Met	01-11-2021	2
12	Not Met	01-12-2021	3	1
34	Met	01-12-2021	1
12	Not Met	01-07-2022	1
12	Not Met	01-08-2022	2
12	Not Met	01-09-2022	3	1
12	Not Met	01-10-2022	4	1
23	Met	01-07-2022	1
23	Not Met	01-08-2022	2
12	Not Met	01-07-2022	1
12	Not Met	01-08-2022	2
12	Not Met	01-10-2022	1
12	Not Met	01-11-2022	2

please help me 

[amitchandak · Accepted solution]

@ssk_1984 , check if this logic can help

Power BI- Customer Purchasing in each of the last 3 months: https://youtu.be/bGtjoccEA38

[v-yetao1-msft · Accepted solution]

Hi @ssk_1984 

First you need add a index column for your table ,because you have some of the exact same lines .

Then create calculated column to group your final id .

IF = 
var _index=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])),[Final ID])
var _index2=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])-1),[Final ID])
return
IF( _index <> _index2,1,0)

Group = SUMX(FILTER(ALL('Table'),'Table'[Index]<=EARLIER('Table'[Index])),[IF])

You will get a result like this :

Finally , create a calculated column to count the ' not met ' > 3 times .

CountRow = 
var _count=CALCULATE(COUNT('Table'[SLA Result]),FILTER(ALLEXCEPT('Table','Table'[Group]),'Table'[SLA Result]="Not Met"))
return IF(_count>=3 && 'Table'[Count]=3 || 'Table'[Count]=4,1,BLANK())

The final result is as shown below .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@ssk_1984 , check if this logic can help

Power BI- Customer Purchasing in each of the last 3 months: https://youtu.be/bGtjoccEA38