I have a scenario where I'm trying to calculate the moving average from actuals in Custom Day time scale. In a module, I have sales data to be uploaded, from the sales data I have to calculate the moving avg of 90 days at the day level. Dimension in the module Article, Custom Day scale. Line Item -Sales Qty, Sales Value.
I tried using the formula -
Line item 1- CUMULATE(Sales Qty, FALSE, Custom Day)) / 90
Sorry i forgot to respond to your follow up on the other post.
Here is how I would probably do this.
You are on the right track with the Cumulate. But we just want to keep a running cumulate going:
From there, we can then calculate what our 10 week ago week would be with a formula like the following: FINDITEM(Fake time, TEXT(VALUE(NAME(ITEM(Fake time))) - 10)) It would be best to stage this out to not calculate it all on one line.
From there, we can see what the current value of the cumulative line is, subtract what it was 10 periods ago, and divide this by 10. : (Cumulate Line - Cumulate Line[LOOKUP: '10 week Ago']) / 10
I am sure there are other ways to go about this as well, but this should work.
Thanks @jasonblinn for your swift response. The average is running, but I have an issue from next week/day the current week Average should be Considered. The above formula works well for moving average. But for forecasting the sales data Clients requesting the last 90 days average and for forecasting days it should include the Current Day Average. Any workaround for this, please?
This is how I would handle it. Create a mapping module from Fake Time to Real Time and use the MovingSum() function on Real Time. Then do a lookup to Real Time from the mapping module to get your data for reporting.