Consecutive Days an event has occured

[spandana]

Hello everyone,   I have been struggling to find a solution to this.  I have a table with 2 columns. The dates [1/1/2021, 1/2/2021.. ], Another column saying 1 if an event has occurred, and blank otherwise. I have been trying to find a way to calculate the max consecutive days an event has occurred.    It would be great if I could get a solution

[spandana]

Thank you! Really helpful!

[v-yalanwu-msft · Accepted solution]

Hi, @spandana ;

According to your desscription, I think Jihwan_Kim’s reply is a good idea. In addition, you also could create a column and measure to calculate. as follows:

1.create a column

group = SUMX(FILTER(ALL(Data),[Date]<=EARLIER(Data[Date])),[Event]-1)

2.create a measure

maxDays = 
var _table=SUMMARIZE('Data',[group],"count",CALCULATE(COUNTROWS('Data'),FILTER(ALL(Data),[group]=MAX([group]))))
return MAXX(_table,[count])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Event consequtive max occurrence =

VAR _eventflagcumulatetable =

ADDCOLUMNS (

SUMMARIZE ( Data, Data[Date] ),

"@eventflagcumulate",

CALCULATE (

SUMX (

FILTER ( ALL ( Data ), Data[Date] <= MAX ( Data[Date] ) ),

Data[Event] - 1

)

)

)

VAR _groupbyeventflagcumulate =

GROUPBY (

_eventflagcumulatetable,

[@eventflagcumulate],

"@countrows", SUMX ( CURRENTGROUP (), 1 )

)

RETURN

MAXX(_groupbyeventflagcumulate, [@countrows])

https://www.dropbox.com/s/4p6n4ktgn0mk4w9/spandana.pbix?dl=0