Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ola7mat
Frequent Visitor

Cumulative count with reset

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:Capture.PNG

 

 

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?

1 REPLY 1
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.