Cumulative sum with conditional offset?

[gregszkilnyk]

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,

 

[JGordon11]

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
	A	B	C	D	E	F	G
1	duration	starting ref		duration	starting ref	accum duration
2	0	0		0	0	0
3	0.2528609	10		0.2528609	10	10
4	0.928987	10		0.928987024	10	10.92898702
5	0.3337418	10		0.333741848	10	11.26272887
6	0.1866453	10		0.186645308	10	11.44937418
7	0.0154029	10		0.015402869	10	11.46477705
8	0.0088705	10		0.008870532	10	11.47364758
9	0.6313025	10		0.631302543	10	12.10495012
10	0.1105961	10		0.110596143	10	12.21554627
11	0.088619	10		0.088618989	10	12.30416526
12	0.2256071	25		0.225607106	25	25
13	0.2420241	25		0.242024093	25	25.24202409
14	0.9959598	25		0.995959833	25	26.23798393
15	0.8738257	25		0.873825664	25	27.11180959
16	0.8766651	25		0.876665088	25	27.98847468
17	0.2340784	25		0.234078412	25	28.22255309
18	0.5627768	25		0.562776823	25	28.78532991
19	0.437755	25		0.437755019	25	29.22308493
20	0.9287569	25		0.928756874	25	30.15184181
21	0.0593037	25		0.059303712	25	30.21114552
22
Sheet2

 

[gregszkilnyk]

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
	A	B	C	D	E	F	G
1	duration	starting ref		duration	starting ref	accum duration
2	0	0		0	0	0
3	0.2528609	10		0.2528609	10	10
4	0.928987	10		0.928987024	10	10.92898702
5	0.3337418	10		0.333741848	10	11.26272887
6	0.1866453	10		0.186645308	10	11.44937418
7	0.0154029	10		0.015402869	10	11.46477705
8	0.0088705	10		0.008870532	10	11.47364758
9	0.6313025	10		0.631302543	10	12.10495012
10	0.1105961	10		0.110596143	10	12.21554627
11	0.088619	10		0.088618989	10	12.30416526
12	0.2256071	25		0.225607106	25	25
13	0.2420241	25		0.242024093	25	25.24202409
14	0.9959598	25		0.995959833	25	26.23798393
15	0.8738257	25		0.873825664	25	27.11180959
16	0.8766651	25		0.876665088	25	27.98847468
17	0.2340784	25		0.234078412	25	28.22255309
18	0.5627768	25		0.562776823	25	28.78532991
19	0.437755	25		0.437755019	25	29.22308493
20	0.9287569	25		0.928756874	25	30.15184181
21	0.0593037	25		0.059303712	25	30.21114552
22
Sheet2

works great, thanks!