Create a calculated field for the Actual field that is similar to the following:
if [Order Date]>today() then 0 else [Actual] end
Use this on your view in place of Actual. Hope this helps! If not, maybe a packaged workbook (twbx file) can be posted?
What I need is for the column with the Moving average to zero off. With the solution you suggested, that i had looked at, the moving average will continue to compute for all periods where I have a forecast as both the forecat and moving average need to display on a single sheet. In the image, once my demand moves to zero (March 2012), I would like my 'Demand: Moving Average' to default to zero, or to remain constant. Either would be acceptable.
You can edit the formula for the moving average the same way, using IF or IIF, based on the date (as shown) or on the "Actual" value for the month being equal to zero or greater than zero.
Tried that already and it does not give the desired result as moving average uses Window_AVG and one cannot "ignore" half the window
I mean something like the following:
I haven't tested its behavior, but the syntax works. You should be able to use IFNULL too. Or test the date instead.
Thanks a lot. That is what i was looking for.