3M rolling average for data less than 3 months old

[sifatnabil]

Hi all,

I have a 3M (or 12 week) rolling average in a text box, but it doesn't work for data less than 3 months old because it always averages the last 3 months even if the product was only launched 1 month ago.

e.g. this is the formula I use in a texbox:

sum([REVENUE])-(Sum({<[CALENDAR DATE]={'>=$(=MonthStart(Max([CALENDAR DATE]), -2))<=$(=MonthEnd(Max([CALENDAR DATE])))'}>}

[REVENUE])/(3))

/

(Sum({<[CALENDAR DATE]={'>=$(=MonthStart(Max([CALENDAR DATE]), -2))<=$(=MonthEnd(Max([CALENDAR DATE])))'}>}

[REVENUE])/(3))

keeps taking the last 3 months even if data does not exist for that product (i.e. assumes zero revenue for months where the product was not launched). This messes up the 3M average. What's the way around this?