I am attempting to create a report that would provide me a month to month break down of revenue based on the opportunity's start date, end date and Estimated_Revenue and the calendar weeks. Given the start and end date, the calendar weeks are calculated as End Date - Start Date/ 7.
However, I still need to calculate revenue per day in a calendar week where our week consists of 5 days. Therefore, I am using the following formula to calculate
Revenue Per Week Per Day: Revenue/(End Date - Start Date/ 7)/5
Example: Start Date: 2014-08-03 End Date: 2014-12-12 Estimated_Revenue_Won: 185960.00
Revenue Per Week Per Day: 1987.35885447934559081471600
August: (2014-08-03 to 2014-08-31)* Revenue Per Day = 55646.04792542167654281204800 .... September: 57633.40677990102213362676400
How can I do something like this? Would this be via Apex Class, visualforce?
However, I do not know how to aggregate all this to get the desired result. I have been struggling with this for quiet a bit, any direction would be extremely helpful.