How to show month end balances in a Pivot Table

[Guest]

I have an electronic statement from my broker that lists daily p/l and the
acct balance. I have built a pivot table over the data, and the MTD P/L
shows fine, but is there a way to get the Pivot Table to show the ending
balance for prior months (and the current balance for the current month)? I
can only get the PTable to total the balance field, which is not correct.
For example:

Statement:
Date P/L Balance
1/31 -300 10000
2/1 500 10500
2/2 -200 10300
2/3 100 10400

Pivot Table:
Month P/L Balance
Jan 1500 10000
Feb 400 10400

Thanks!

 

[Roger Govier]

Hi

What you are showing as the PT result looks correct to me, is that what
you are achieving or is that what you are saying you want to achieve?
If the latter, then it can be done but you do not need to include the
Balance column in your PT (nor in your source data for that matter).
As you have it set up, drag the P/L field to the Data area for a second
time, it will be labelled Sum of P/L2.
Double click on the field Label, change the name to Balance (if you
don't have the field Balance included in your source data) or
some other appropriate title.
Click on the "Show source data as" dropdown button, and Select Running
Total In and select Date as your "Base" item.
Drag the Data button on the PT to the Total area, and you will get the
two values side by side.
You will need to insert 1 dummy row in your data dated 01/01/2005 with a
P/L figure of 10300, which is the cumulative of all transaction prior to
those show, which then gives rise to a closing balance of 10000 at the
end of Jan.