topic Re: Count the Consecutive values 'not met' by group by dates in DAX Commands and Tips

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

ssk_1984 — Mon, 28 Nov 2022 10:49:20 GMT

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

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

amitchandak — Mon, 28 Nov 2022 15:16:00 GMT

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-Difference/m-p/1290382#M592
https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1290346

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

v-zhangti — Tue, 29 Nov 2022 09:18:30 GMT

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.