Cumulative sum with conditional offset?

gregszkilnyk

New Member
Joined
Dec 20, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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,


1640018319390.png
 

Excel Facts

Links? Where??
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
 
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
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?

Disable AdBlock

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
Back
Top