# Cumulative sum with conditional offset?

#### gregszkilnyk

##### New Member
Hello,

I am trying to create a column in PowerBI that takes the cumulative sum of a table column of numbers, *but*, if in any row the value of that sum is exceeded by a number in a secondary column, the cumulation is replaced with that secondary number. The cumulative sum would then carry on for following rows. Basically, I am trying to recreate the Excel logic shown below in Power Query or a DAX calculated column.

Given that you cannot seem to self-reference columns in PQ or DAX, this seems challenging to replicate. Am I missing a function (I'm relatively new to PQ and DAX)? Are there any common workarounds for this?
Thanks,

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
lst = List.Skip(List.Accumulate(Table.ToRows(Source), {0}, (s,c)=> let lls = List.Last(s) in if c{1} > lls + c{0} then s & {c{1}} else s & {lls+c{0}}),1),
tbl = Table.FromColumns(Table.ToColumns(Source) & {lst}, Table.ColumnNames(Source) & {"accum duration"})
in
tbl``````

Book1
ABCDEFG
1durationstarting refdurationstarting refaccum duration
200000
30.2528609100.25286091010
40.928987100.9289870241010.92898702
50.3337418100.3337418481011.26272887
60.1866453100.1866453081011.44937418
70.0154029100.0154028691011.46477705
80.0088705100.0088705321011.47364758
90.6313025100.6313025431012.10495012
100.1105961100.1105961431012.21554627
110.088619100.0886189891012.30416526
120.2256071250.2256071062525
130.2420241250.2420240932525.24202409
140.9959598250.9959598332526.23798393
150.8738257250.8738256642527.11180959
160.8766651250.8766650882527.98847468
170.2340784250.2340784122528.22255309
180.5627768250.5627768232528.78532991
190.437755250.4377550192529.22308493
200.9287569250.9287568742530.15184181
210.0593037250.0593037122530.21114552
22
Sheet2

Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
lst = List.Skip(List.Accumulate(Table.ToRows(Source), {0}, (s,c)=> let lls = List.Last(s) in if c{1} > lls + c{0} then s & {c{1}} else s & {lls+c{0}}),1),
tbl = Table.FromColumns(Table.ToColumns(Source) & {lst}, Table.ColumnNames(Source) & {"accum duration"})
in
tbl``````

Book1
ABCDEFG
1durationstarting refdurationstarting refaccum duration
200000
30.2528609100.25286091010
40.928987100.9289870241010.92898702
50.3337418100.3337418481011.26272887
60.1866453100.1866453081011.44937418
70.0154029100.0154028691011.46477705
80.0088705100.0088705321011.47364758
90.6313025100.6313025431012.10495012
100.1105961100.1105961431012.21554627
110.088619100.0886189891012.30416526
120.2256071250.2256071062525
130.2420241250.2420240932525.24202409
140.9959598250.9959598332526.23798393
150.8738257250.8738256642527.11180959
160.8766651250.8766650882527.98847468
170.2340784250.2340784122528.22255309
180.5627768250.5627768232528.78532991
190.437755250.4377550192529.22308493
200.9287569250.9287568742530.15184181
210.0593037250.0593037122530.21114552
22
Sheet2
works great, thanks!

Replies
4
Views
381
Replies
7
Views
2K
Replies
0
Views
762
Replies
2
Views
78
Replies
3
Views
877

1,203,082
Messages
6,053,424
Members
444,662
Latest member
AaronPMH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back