Forum Discussion

spandana's avatar
spandana
Frequent Visitor
5 years ago
Solved

Consecutive Days an event has occured

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

Need Help
Tips and Tricks
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    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.

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

     

    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])
     
     
     
     
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.