I am attempting to create a financial balance sheet model and I am having difficulty in calcualting the aggregate balances. Here is what I have so far...
My Source data looks something like this... Note that there are more periods and lots more transactions from many different locations.
| Date | Period | Year | JE Description | Trans Amount |
| 2/1/11 | 2 | 2011 | Beg Bal | 10,000 |
| 3/1/11 | 3 | 2011 | Beg Bal | 11,000 |
| 4/1/11 | 4 | 2011 | Beg Bal | 9,500 |
| 2/10/11 | 2 | 2011 | Sold | -1,000 |
| 2/20/11 | 2 | 2011 | Purchased | 2,000 |
| 3/10/11 | 3 | 2011 | Sold | -3,000 |
| 3/20/11 | 3 | 2011 | Purchased | 1,500 |
| 4/10/11 | 4 | 2011 | Sold | -3,500 |
| 4/20/11 | 4 | 2011 | Purchased | 2,000 |
Here is what I want to get when I select periods 2, 3 and 4 (Feb - Apr)
| 1 | 2 | 3 | 4 |
| Balance | Change | History | Detailed Balance |
| Inventory | 8,000 | -2,000 | 8,000 | 0 |
| Feb | 11,000 | 1,000 | 11,000 | 11,000 |
| Mar | 9,500 | -1,500 | -1,500 | 9,500 |
| Apr | 8,000 | -1,500 | -1,500 | 8,000 |
I can calculate 2 the change as follows
num(sum( {$<Year = {$(=Max(Year))},[JE Description] -= {'Beg Bal'} >} [Transaction Amount]), '#,##0')
I can calculate 3 the history as follows
num(sum( {$<Year = {$(=Max(Year))}, Period = {$(=Min(Period))}, [JE Description]={'Beg Bal'} >} [Transaction Amount]) +
sum( {$<Year = {$(=Max(Year))}, Period = {">=$(=Min(Period))<=$(=Max(Period))"}, [JE Description] -={'Beg Bal'} >} [Transaction Amount]), '#,##0')
I can calculate 4 the detailed balances as follows (4 is not neccessary if I can get 1)
num
(
RangeSum(Above(
sum( {$<Year = {$(=Max(Year))}, Period = {$(=Min(Period))}, [JE Description]={'Beg Bal'} >} [Transaction Amount]) +
sum( {$<Year = {$(=Max(Year))}, Period = {">=$(=Min(Period))<=$(=Max(Period))"}, [JE Description] -={'Beg Bal'} >} [Transaction Amount])
, 0, RowNo()))
, '#,##0')
I can not calculate 1. My issue arises trying to calculate the aggregate. The individual periods are correct but the aggregate does not work out. Instead of 8,000 which is my closing balance for April all I can get is 28,500 which includes all beginning balances.
