I am in need of some help generating a value on a monthly basis that is equal across the months. The current scenario is, I have a From Date, Duration and a Total Value. What I want to achieve is, based on the duration distribute the Total Value starting from the From Date across months for example:
Total Value: 3000
From Date: 04/08/14
Ideally I want to divide the Total Value by the duration (Total Value/Duration) and distribute it across 3 months from the 04/08/14, resulting in:
Aug 2014 = 1000
Sept 2014 = 1000
Oct 2014 = 1000
I have attempted this so far by creating a To Date based on the duration and then using a iterno() and while statement. This works to an extent as it effectively creates a daily rate opposed to a monthly rate however as it is based on days rather than months when you group the values by Month the Values are not equal as there are less days in Sept etc. The Total will still equal 3000 but when reporting in the month the value is incorrect as my requirement is to equally distribute the Total Value across the months.
Any help would be much appreciated.