cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jkilb
Helper III
Helper III

Conditional Cumulative Calculated Column

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. 

 

 

 

WelldatersupGoal Column 
justin6/1/2021 5:0071
justin6/1/2021 5:1071
justin6/1/2021 5:2071
justin6/1/2021 5:3011
justin6/1/2021 5:4071
justin6/1/2021 5:5000
justin6/1/2021 6:0072
justin6/1/2021 6:1072
justin6/1/2021 6:2000
justin6/1/2021 6:3073
justin6/1/2021 6:4073
brett6/4/2021 2:1071
brett6/4/2021 2:2071
brett6/4/2021 2:3000
brett6/4/2021 2:4072
brett6/4/2021 2:5012
brett6/4/2021 3:0000
brett6/4/2021 3:1073
3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi, @Jkilb 

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

 

 


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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
Community Support
Community Support

Hi @Jkilb 

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!