Consecutive Row Count from date between Current Month and follow up 2 month

[ssk_1984]

Hi Team,

Pleaes help me to create measure to get the below results

Vertical	REPORT_DATE	SLA_MISSES	Current Month & Follow Two Months same value
BFS	01-01-2022	Met
BFS	01-02-2022	Not Met	1
BFS	01-03-2022	Not Met
BFS	01-04-2022	Not Met
CMT	01-01-2022	Not Met	1
CMT	01-02-2022	Not Met	1
CMT	01-03-2022	Not Met
CMT	01-04-2022	Not Met
Digi. Fin.	01-01-2022	Met
Digi. Fin.	01-02-2022	Met
Digi. Fin.	01-03-2022	Met
HC	01-01-2022	Met
HC	01-02-2022	Met
HC	01-03-2022	Met
INS	01-01-2022	Met
INS	01-02-2022	Met
INS	01-03-2022	Met
LS	01-01-2022	Met
LS	01-02-2022	Met
LS	01-03-2022	Met
RHCG	01-03-2022	Met

from the above table of info, I want to count as one, when Current Month and followed by two Months is 'Not Met', Then current month counted as one. else no action.

Here Ref vertical 'CMT' from Jan to Apr, we have all 'not met' only, Considering Jan it should give me a value 1, Because Jan, feb & Mar has same value.

For feb Also 1, Current month Feb is not met and the consecutive 2 months not met, Mar & Apr...then Feb count as 1.

[ssk_1984]

I have wrongly given the dataset, I working fine...Gr8...Thank you very much

[tamerj1 · Accepted solution]

Hi @ssk_1984 
Please refer to attached sample file with the solution

Current Month & Follow Two Months same value = 
VAR CurrentDate = 'Table'[REPORT_DATE]
VAR CurrentVerticalTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Vertical] ), 'Table'[REPORT_DATE] >= CurrentDate )
VAR T1 = ADDCOLUMNS ( CurrentVerticalTable, "@Rank1", RANKX ( CurrentVerticalTable, [REPORT_DATE],, ASC, Dense ) )
VAR T2 = FILTER ( T1, 'Table'[SLA_MISSES] = "Not Met" )
VAR T3 = ADDCOLUMNS ( T2, "@Rank2", RANKX ( T2, [REPORT_DATE],, ASC, Dense ) )
VAR T4 = FILTER ( T3, [@Rank1] = [@Rank2] )
VAR Result = IF ( COUNTROWS ( T4 ) >= 3, 1 )
RETURN
    Result

[ssk_1984]

Thank you for your time, 

In this first one coming as wrong, for Dec...i dont have consecutive not met for Jan & Feb..But appearing as one count