Cumulative total of RangeSum with Set Expression

[data_RN]

Hello All, 

I am stumped.  I need to get the total of a field where we are showing cumulative sum using rangesum in a table (by program).  We don't want to sum all for this field though, we need to limit total to current year and beyond.

Here is the table displayed currently:

The formula for the last column is correct.  I now need to only display the rows with the red highlight (the sum total for the displaying years for each program).  

Something like this:

Program	FCF (Total)
61	-7.46
86	131.79
62	1.26

I have tried many combinations of max(aggr...) but I can't seem to get it right. 

Can someone help me out?  Or any suggestions?

Here is my cumulative formula (the last column highlighted above in green):

RangeSum(Above(
(Sum ( {<Version_PROG = {'Profit & Loss'}
,VersionName_PROG={'FCF'}
>} ValueGroupCurrency_PROG ))

*
(1/(Pow(1+(
Avg(distinct{$<Version_PROG= {'Financials'}
,VersionName_PROG= {'WaCC'}

>}total Quantity_PROG) ),RowNo(total)-1)))

,0,RowNo()))

As always....thank you in advance!!!

[F_B]

Hi @data_RN ,

if you have a field that represents the year in your data model, you can try adding it in your formula like this:

RangeSum(
Above(
Sum(
{<Version_PROG={'Profit & Loss'},
VersionName_PROG={'FCF'},
Year={'$(=Max(Year))'}>}
ValueGroupCurrency_PROG
)
* (1 / Pow(1 + Avg(distinct{<Version_PROG={'Financials'}, VersionName_PROG={'WaCC'}>} Total Quantity_PROG), RowNo(total) - 1)),
0,
RowNo()
)

[data_RN]

Thanks @F_B .  I've tried it similarly, but it seems to need the previous cells to calculate using the "Above" action.   I haven't used it before, but when I remove the year, the calculation totals are completely wrong.  Is there an easy way to limit the years between current year and max?

[F_B]

How about this?

RangeSum(
Above(
Sum(
{<Version_PROG={'Profit & Loss'},
VersionName_PROG={'FCF'},
Year={">=$(=Year(Today()))<=$(=Max(Year))"}>}
ValueGroupCurrency_PROG
)
* (1 / Pow(1 + Avg(distinct{<Version_PROG={'Financials'}, VersionName_PROG={'WaCC'}>} Total Quantity_PROG), RowNo(total) - 1)),
0,
RowNo()
)