Conditional Cumulative Calculated Column

[Anonymous]

Hi, I would like to create a cummulative counting column that adds 1 when [rsup] becomes equal to "0". The process order should follow [date] (like in the example below, descending). Once the column becomes "0", the [Goal Column] should equal "0". Once it changes to anything but a "0", it should add 1 to the existing [Goal Column] value. 

As you can see, there are multiple [Well] categories, so it should be filtered on this. 

Thank you and let me know if you need an additional description of the problem! I really appreciate your help, I've been stuck with this for a while. 

Well	date	rsup	Goal Column
justin	6/1/2021 5:00	7	1
justin	6/1/2021 5:10	7	1
justin	6/1/2021 5:20	7	1
justin	6/1/2021 5:30	1	1
justin	6/1/2021 5:40	7	1
justin	6/1/2021 5:50	0	0
justin	6/1/2021 6:00	7	2
justin	6/1/2021 6:10	7	2
justin	6/1/2021 6:20	0	0
justin	6/1/2021 6:30	7	3
justin	6/1/2021 6:40	7	3
brett	6/4/2021 2:10	7	1
brett	6/4/2021 2:20	7	1
brett	6/4/2021 2:30	0	0
brett	6/4/2021 2:40	7	2
brett	6/4/2021 2:50	1	2
brett	6/4/2021 3:00	0	0
brett	6/4/2021 3:10	7	3

[Jihwan_Kim]

Hi, @Anonymous 

Please check the sample pbix file's link down below.

https://www.dropbox.com/s/tq8jyocbvb32hj6/jklibv2.pbix?dl=0 

step one CC =

VAR currentwell = 'Table'[Well]

VAR currentdatetime = 'Table'[date]

VAR previousdatetime =

CALCULATE (

MAX ( 'Table'[date] ),

FILTER (

'Table',

'Table'[Well] = currentwell

&& 'Table'[date] < currentdatetime

)

)

RETURN

SWITCH (

TRUE (),

'Table'[rsup] = 0, 0,

CALCULATE (

SUM ( 'Table'[rsup] ),

FILTER (

'Table',

'Table'[Well] = currentwell

&& 'Table'[date] = previousdatetime

)

) <> 0, 0,

1

)

Goal Column CC =

VAR currentwell = 'Table'[Well]

VAR currentdatetime = 'Table'[date]

VAR previousdatetime =

CALCULATE (

MAX ( 'Table'[date] ),

FILTER (

'Table',

'Table'[Well] = currentwell

&& 'Table'[date] < currentdatetime

)

)

RETURN

IF (

'Table'[rsup] = 0,

0,

SUMX (

FILTER (

'Table',

'Table'[Well] = currentwell

&& 'Table'[date] <= currentdatetime

),

'Table'[step one CC]

)

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Thank you for the quick response and support - however, after running the first formula, I'm receiving the following error: 

"There's not enough memory to complete this operatino. Please try again later whne there may be more memory avilable". 

My dataset currently has ~25 million rows. Is this something I should do in the query editor? Perhaps with Python or M? 

Thanks!

[v-jingzhang]

Hi @Anonymous 

You could consider reducing the data model size by removing some unnecessary rows/columns in Power Query Editor. There are some tips about how to reduce data model size for your reference:

3 ways to REDUCE DATA In Power BI Desktop (No Premium needed!) - YouTube

Top 5 tips to reduce your Power BI data model size — Apex Insights: Power BI tips & tricks

Data reduction techniques for Import modeling - Power BI | Microsoft Docs

Or you can use a subset of data to test the formulas when you create a report.

Regards,
Community Support Team _ Jing