Count the Consecutive values 'not met' by group by dates

[ssk_1984]

Hi,

Please help to add column to count the repetation of values by grouping the Date Columns.

Final ID	SLA Result	Report Date	Count
12	Not Met	01-10-2021	1
12	Not Met	01-11-2021	2
12	Not Met	01-12-2021	3
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

Here, Group dates by consecutively, If any blank/stop on the dates, again the group count it from 1,2,3

abouve data, Final 12, and not met contineously appearing Oct 2021 to Dec 2021, then values appearing as 1,2 ,3 

after no data mis from Jan to Jun, It starts from Jul 22 to Nov 2022. Then the count should starts again form 1,2 ,3,4

Please help 

[v-zhangti · Accepted solution]

Hi, @ssk_1984 

You can try the following methods.
Column:

Previous Date = MAXX(FILTER(ALL('Table'),[Report Date]<EARLIER('Table'[Report Date])),[Report Date])

Diff = DATEDIFF([Previous Date],[Report Date],MONTH)

First day = IF([Diff]<>1,1,BLANK())

Consecutive days = 
VAR _lastdate =
    CALCULATE ( MAX ('Table'[Report Date]),
        FILTER ('Table',
            [Report Date] <= EARLIER ( 'Table'[Report Date])
                && [First day] = 1 ) )
Return
    CALCULATE (
        COUNT('Table'[Report Date]),
        FILTER ('Table',[Report Date]>= _lastdate
                && [Report Date] <= EARLIER ( 'Table'[Report Date]) ) )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@ssk_1984 , refer Continuous streak

Continuous streak : https://youtu.be/GdMcwvdwr0o

refer

Continuous Streak -https://community.powerbi.com/t5/Desktop/Need-help-in-DAX/m-p/1277302#M559393
https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Continuous-Streak-One-Day-Differenc...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...