I have a pivot table and I want to create multiple columns that will end up like the table below (on the right, left is sample data). My data has date processed for each line so I can do a date sort, but I can only do one column at a time(e.g. rent, then copy it, then resort the table for grand total, then manually make a new percent column). Is there a way to set it up so I have My data separated on a year-to-year basis but have that year to year start on 7/1/? Maybe calculated columns?
<tbody>
</tbody>Thanks
Sample Data | |||||||||
Date Processed | Cost Type | Amount | FY 2010 Grand Total | FY 2010 Rent | FY 2010 Rent % | FY 2011 Grand Total | FY 2011 Rent | FY 2011 Rent % | |
10/11/2010 | Rent | $ 47,882.00 | $ 134,299.00 | $ 47,882.00 | 35.65% | $ 72,503.00 | $ 20,978.00 | 28.93% | |
10/12/2010 | Materials | $ 86,417.00 | |||||||
10/14/2011 | Materials | $ 14,638.00 | |||||||
10/15/2011 | Materials | $ 16,207.00 | |||||||
10/16/2011 | Rent | $ 20,978.00 | |||||||
10/17/2011 | Labor | $ 20,680.00 |
<tbody>
</tbody>