I have a datatable that contains several columns including "ext price", "master system", "system", "coverage From date", and "coverage to date".
So the table has the total amount I spent on something (i.e. what the check amount was), and the beginning and end dates of the coverage (it's service contracts).
My leader wants to see how much I spent "per month" for each system. So if I wrote a $2,000 check in June and that check covers maintenance for say... July 2013 to June 2014 he wants a report that show $166.66/month for that system starting in July and ending in the next June column.
I have figured out how to calculate the number of months in the coverage period, and how to use that to calculate the monthly "amount" per transaction but how do I create a report (or pivot table) that shows that amount each month instead of lumping it into the first month?
In "financial" terms I think this is referred to showing the money as a yearly accural...?
I am attaching a very simple spreadsheet view of the data.