Cumulative count with reset

[ola7mat]

Hello,

I know you are probably thinking oh dear here we go again not another one...

And for this I apologise however I cannot seem to get my head around how to achieve this and looking for your help!

I have a table that looks like this:

I am trying to achieve the results shown in the 5th column with formula (below) that is seen in the 7th column.

It all works great until row 7 when it starts to go wrong...

The formula i am using is:

CountConsecutiveValues (Each Category) = 
VAR LastTableDate = 'Table'[Date]
VAR CumulativeValues = 'Table'[SumCumulativeValues (Each Category)]
RETURN
        MAX(
                COUNTX (
                        CALCULATETABLE (
                                'Table',
                                ALLEXCEPT ( 'Table', 'Table'[Category] ),
                                'Table'[Date] <= LastTableDate,
                                'Table'[SumCumulativeValues (Each Category)] < CumulativeValues
                            ),
                        'Table'[Values]
                        ),
                IF('Table'[Values]>0,1,0)
        )

Any suggestions where i am going wrong?

[Greg_Deckler]

I'm guessing that you need to take a look at the EARLIER function. So, basically what the pseudo-code should look like would be something along the lines of:

Column = 

VAR __tmpTable = FILTER(ALL('Table'),[Id]<EARLIER([Id])) //return all rows before current row

VAR __maxBlank = MAXX(FILTER(__tmpTable,ISBLANK([Values]),[Id]) //return the highest index where Values is blank

RETURN COUNTROWS(FILTER(__tmpTable,[Id]>__maxBlank)) //returns count of rows from the temp table that are past the highest blank row Id