Sum accumulated amount over the years using pivot table

[#1]

Hi,

I have a pivot table with time dimension and trying to sum the accumulated amount for the selected year(s). The sum should take the selected max date and sum all the way back over the years taking into consideration the time dimension.

So let us say we have this data table:

	2011	2012	2013	2014	2015
Cash	$100	$100	$100	$100	$100
Restricted Cash	$50	$50	$50	$50	$50

So in case 2012, 2014 and 2015 are selected, the results in the pivot table should be:

	2012	2014	2015
Cash	$200	$400	$500
Restricted Cash	$100	$200	$250

I've tried these expressions but non have worked the way should:

sum({1<[Transaction Date] = {"<=$(=max([Transaction Date]))"}>}[CASH])        //(It ignores all the selected years and sums year-by-year)

sum(TOTAL{$<[Transaction Date] = {"<=$(=max([Transaction Date]))"}>}[CASH])    

sum(TOTAL{1<[Transaction Date] = {"<=$(=max([Transaction Date]))"}>}[CASH])     (same amount for the max year )

sum(total{$<Year =, Month=,Quarter=,[Transaction Date] = {"<=$(=max([Transaction Date]))"}>}[CASH])

Any help will be appreciated. Thanks!

[ramoncova06]

you can use rangesum and before for this

RangeSum(sum({<[Transaction Date]={"<=$(=max([Transaction Date]))"}>}Cash),

Before(sum({<[Transaction Date]={"<=$(=max([Transaction Date]))"}>}Cash),0,ColumnNo())

)

Accumulative Sums

[sunny_talwar]

Is this what you want?

Dimension F1 and Years

Expression: =If(Sum(Data) > 0, RangeSum(Before(Sum({<Years = >}Data), 0, ColumnNo())))

PFA the application for reference.

Best,

Sunny