I work with monthly bank data where I have a total of additions and subtractions each month, like this:
| Month End | Deposits | Expenses | Running Total |
| Beginning Balance → | 10,714.67 | ||
| 1/7/2013 | 1,200.00 | 1,920.76 | 9,993.91 |
| 2/6/2013 | 3,723.64 | 2,143.77 | 11,573.78 |
| 3/6/2013 | 3,949.09 | 7,480.07 | 8,042.80 |
I can get PowerPivot to do a running total, but I need it to start with a beginning balance as shown in the "Running Total" column here. This table is filtered by account number so the beginning balance is for the selected account, which is in a separate table from the transaction data. Can you give me any suggestions on how to do this?
Hi Luciana,
It's difficult to say without seeing your model. Can you please share a mock up of your file with the tables relevant to this question. Also show your desired result based on the data in the model so we can follow the logic.
Thanks,
Mynda
Mynda,
Please see the attached mockup. The bank account transactions are on the "Main" tab and the lookup info for each account is on the "AccountList" tab. I can get the pivot table to give me the total dollar amount of the transactions for each month, but I need it to add a running total with the beginning balance for the selected account referenced from the AccountList, as shown on the "MoBal" tab. It should be dynamic and change with the "Stmt Acct #" filter at the top, as different accounts will have different numbers of months.
Thanks,
Luciana
Hi Luciana,
Thanks for the mockup. Will the AccountList only ever contain one balance for each account, or will there be more balances added as time goes by, therefore requiring the formula to find the latest balance for each account?
Mynda
Mynda,
There will only be one beginning balance per account.
Thanks,
Luciana
Hi Luciana,
When working with dates you need to create a date/calendar table. I also had to create a separate dimension table for your accounts list so that the relationships could be set up correctly.
See file attached.
Note: the PivotTable will show the dates up to the last date in your Main table, i.e. October 31, 2015. If you want, you can add month and year fields to your date table and use those fields in your PivotTable to summarise it further.
Mynda
Thank you so much Mynda!! That got me going in the direction I needed. I wrapped an additional "IF" statement around it so it would only calculate for dates that had activity, to eliminate all the extra blank rows. Perfect!
Cheers,
Luciana
