Help with Pivot table

[dhasharadh]

Hi All, I need some help on the below. red once are the required outputs. @sunny_talwar @swuehl 

YearMonth	202201	202202	202203	202204	202205
Adds	1000	2000	1500	2000	2000
Delete	500	1000	1000	1000	900
Base	500	1500	2000	4000	5100
Avg Base	225	1000	1750	3000	4550

Final = Previous Month Base + Adds - Delete , Ex : for 202201 previous month base is 0 + 1000(adds) - 500 (Delete)

Avg Base = (Previous Month Base + Curent Month Base)/2. Ex: for 202201 (0 + 500)/2. 

[rubenmarin · Accepted solution]

Hi, if Adds is "Sum({<Type={'Adds'}>} Amount)" and Delete is "Sum({<Type={'Delete'}>} Amount)"

- Base could be: RangeSum(Before(Sum({<Type={'Adds'}>} Amount)-Sum({<Type={'Delete'}>} Amount), 0, ColumnNo()))

- And the Avg Base: (RangeSum(Before(Sum({<Type={'Adds'}>} Amount)-Sum({<Type={'Delete'}>} Amount), 0, ColumnNo()))
+RangeSum(Before(Sum({<Type={'Adds'}>} Amount)-Sum({<Type={'Delete'}>} Amount), 0, ColumnNo()-1)))/2

[dhasharadh]

Many Thanks @rubenmarin for Base the expression is working, but for Avg Base is not working as expected.

[rubenmarin]

Hi, and what is the expected result? in the example it only does a division of that column with the previous, and that's what the expression does.