Consecutive sum

[NKTMS]

Hi,

I've been stuck on this for a while. 
I have this table in PowerBI with Columns Lkey,Mkey and Count. Nothings unique in that. I want to create a new column Sum which sums up the values of count  till a blank value occurs and then it restarts again as showing in the figure. 

I'm a bit confused on the best method to find it. It can be on the same table. Or can we create another table an populate it with the sum with corresponding Lkey and Mkeys?

[NKTMS]

Hi, First of all, thanks for the solution. It is working out for me. The only issue is that it takes hours to run this. I optimized the table but still its taking a long time. Any idea on how to optimize the code?
To make the code run properly i added two conditions, LK=PREVIOUS(LK) and MK=PREVIOUS(MK) in the variables pre_blank, _Value and next_blank.

[v-yueyunzh-msft · Accepted solution]

Hi , @NKTMS 

Thanks for your sample data and end result first!

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can add an index column in Power Query to judge the row .

(3)Then we can apply the data to Power BI Desktop, and we can cretae a calculated column.

I create two calculated columns and you can choose which the column you need to get :

Column 1 = var _pre_blank =  FILTER('Table','Table'[Index]<EARLIER('Table'[Index]) && 'Table'[Count] =  BLANK())
var _blank_index =IF(ISBLANK( MAXX(_pre_blank,[Index])), 0, MAXX(_pre_blank,[Index]))
var _value = SUMX( FILTER('Table' , 'Table'[Index]>_blank_index && 'Table'[Index]<=EARLIER('Table'[Index])) , [Count])
return
_value

Column 2 = var _pre_blank =  FILTER('Table','Table'[Index]<EARLIER('Table'[Index]) && 'Table'[Count] =  BLANK())
var _blank_index =IF(ISBLANK( MAXX(_pre_blank,[Index])), 0, MAXX(_pre_blank,[Index]))
var _value = SUMX( FILTER('Table' , 'Table'[Index]>_blank_index && 'Table'[Index]<=EARLIER('Table'[Index])) , [Count])
var _next_blank =   FILTER('Table','Table'[Index]>=EARLIER('Table'[Index]) && 'Table'[Count] =  BLANK())
var _blank_next=IF( ISBLANK( MINX(_next_blank,[Index]) ) , MAX('Table'[Index])+1 , MINX(_next_blank,[Index]))
return
IF(_blank_next -1 = [Index] , _value, BLANK())

Then we can get the result as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[NKTMS]

Hi Thanks for the solution, although i did come across something. It restarts the sum if the value in count column is 0 but I only want it to restart only if it is blank. I hope you can help me with it.