Period over Period Account Balance Difference %

Fri, 08 Jan 2016 18:32:45 GMT

What is the best way to calculate difference is account balance.

Overview of what I have done so far:

I have added two sheet with two different approach:
- 1st approach is creating pivot table and calculating differences in account balance considering before function
- 2nd approach is creating straight table and calculating differences in account balance using above function
Pending issues:
- Period Slider is not dynamic
- Difference sort order

Calculation

Account Balance: Sum(Amount)

Difference: if(Period='2014/1',0, sum( Amount)- before(sum(Amount)))

Difference %: fabs(if(Period='2014/1',0,sum( Amount)- before(sum(Amount)))/sum( Amount))

Re: Period over Period Account Balance Difference %

marcus_sommer — Sat, 09 Jan 2016 10:51:35 GMT

I think you need to wrap your before-expression within an aggr-function - see here Calculating rolling n-period totals, averages or other aggregations to the example of the rolling 3-months which is quite similar to your task.

- Marcus