Need Consecutive Status count based on Date

 

Count_CC = 
VAR __dt = Sample_Data[Date]
VAR __st = Sample_Data[Status]
VAR __indx1 =
    MAXX(
        TOPN(
            1,
            FILTER( Sample_Data, Sample_Data[Date] < __dt && Sample_Data[Status] <> __st ),
            Sample_Data[Index]
        ), Sample_Data[Index]
    )
VAR __indx2 =
    MAXX(
        TOPN(
            1,
            FILTER( Sample_Data, Sample_Data[Date] > __dt && Sample_Data[Status] <> __st ),
            Sample_Data[Index], 1
        ), Sample_Data[Index]
    )
RETURN
    IF(
        ISBLANK( __indx2 ),
        MAX( Sample_Data[Index] ) - __indx1,
        __indx2 - __indx1 - 1
    )

 

Solution in Power Query is way much conciser,

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdCxCoNAFETRXwlbC+u8VVc/INbpxWLLQFLZ5PMTi4Tc6R4cHlxm25KycvSh1KXrqz0v53Frx5H27sQCDOIILMQKHIgLcCSKRdPnWNv98VUmVVM2zaaMWkxZpZ4cNpSM2SXbKhimYt8sk+0VlmaLFUub/jlyUCt1oM4/3d8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Exam Name" = _t, Status = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Status"}, {{"ar", each _}, {"Count_PQ", each Table.RowCount(_), Int64.Type}}, 0, (x,y) => Number.From(x<>y)),
    #"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"Date", "Exam Name"}, {"Date", "Exam Name"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded ar", "Index", 1, 1, Int64.Type)
in
    #"Added Index"