DAX formula to amortization revenue over 12mths, 36mths etc

[KimC2504]

Hi All,

I have all my orders data in a power pivot model and I am trying to automate the revenue amortization as well. I don’t know if this possible but could someone give me some ideas how to approach this problem please?

I need to amortize revenue over different periods, could be 12 months, 36months, 48months etc. If the revenue is amortized over 1 year, I can get the revenue for one month just fine, but I cannot work out how to get the revenue for the next 11 months. I would also like the monthly revenue to start the next month eg if 1/4/2014 to start 1/5/2014

I have the following data table:[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Type[/TD]
[TD]Date[/TD]
[TD]Amortize[/TD]
[TD]Mth Rev[/TD]
[/TR]
[TR]
[TD]Aust[/TD]
[TD]AAA[/TD]
[TD]1/4/2014[/TD]
[TD]36mths[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]NZ[/TD]
[TD]AAB[/TD]
[TD]1/5/2014[/TD]
[TD]12mths[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]AUST[/TD]
[TD]DOG[/TD]
[TD]1/6/2014[/TD]
[TD]24mths[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Aust[/TD]
[TD]CAT[/TD]
[TD]1/4/2014[/TD]
[TD]12mths[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

PowerPivot example (note, extract only not all future months included)[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Type[/TD]
[TD]1/4/2014[/TD]
[TD]1/5/2014[/TD]
[TD]1/6/2014[/TD]
[TD]1/7/2014[/TD]
[TD]1/8/2014[/TD]
[TD]1/9/2014[/TD]
[TD]1/10/2014[/TD]
[TD]1/11/2014[/TD]
[/TR]
[TR]
[TD]Aust[/TD]
[TD]AAA[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]NZ[/TD]
[TD]AAB[/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Aust[/TD]
[TD]DOG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Aust[/TD]
[TD]CAT[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]