How to create formula for YTD and YTG run rate (Moving average)

I would like to create formula to calculate moving average for YTD and YTG. Here's the formula i input MOVINGSUM(Actual, -11, 0, AVERAGE) to calcuate YTD run rate, MOVINGSUM(Remaining, -11, 0, AVERAGE) to calculate YTG run rate but the it doesn't seem correct to calculate the moving average.

Kindly see example from file attached.

thanks

Alice

FC_Spending_Summary_Run_Rate_74274.xls

Accepted answers

All comments

Misbah

@tz9lawa

In your formula you are hardcoding the numbers and taking the average of 11 months. I would say make that dynamic and check how many periods are there in YTD actuals and how many are there in YTG actuals. (Also Use Actuals in both the line items - though I am not sure what the exact ask is). However you can still do below

Use that dynamic number in your formula

MOVINGSUM(Actual, -11, 0, AVERAGE)

MOVINGSUM(Remaining, -11, 0, AVERAGE)

Alternately I see you are already pulling the values by using SELECT YTD or SELECT YTG, if you do this way then you can just divide that number by the number of counts.

Hope that helps

Misbah

tz9lawa

Sorry, Maybe my question confused you.

My financial year start from Jul19-Jun20.

YTD - Jul19-May20 (Already 11 period)

YTG-Left Jun20 (1 month to go).

Need your advice how to calculate the run rate base on YTD number of period, YTG number of period without hardcoding.

thanks

Alice

Misbah

@tz9lawa

Step 1: Check thru formula how many periods are there in YTD Calcs like this

a) IF END() <= CURRENTPERIODEND() THEN 1 ELSE 0

b) IF END() > CURRENTPERIODEND() THEN 1 ELSE 0

Step 2: Count or Sum these numbers from Step 1a. In your case it should come to 11

Step 3: Count or Sum these numbers from Step 1b. In your case it should come to 1

Step 4: Use Line item from Step 2 in your YTD Formula instead of 11

Step 5: Use Line item from Step 3 in your YTG Formula instead of 1

The above process is only to show you how you can replace hard coded numbers with dynamic ones

nathan_rudman

why not using the YTD and YTG time items of Anaplan ?

Quick Links

Can you help?

Unanswered questions

Compare/Sync new module that is working properly from Dev to PROD model _no amounts in PROD
I have created a Module "report" in DEV and it is working properly there, then I used Compare/Synce to bring into PROD, but for some reason it is not working properly in PROD. I have not amounts in PROD. I did check for any missing constants. Can anyone help with this issue or any advise?
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…