Forecast based on Actual

[iwjvanaerschot]

I am stuck with a running forecast calculation and hopefully someone can help me out! 

On a weekly basis I receive a sales forecast and production forecast for several weeks ahead. I want to calculate the forecasted stock based on the latest actuals. 

So for example: Currently we are in week 5, I know my current stock and I know for the coming weeks the forecasted production and the forecasted sales, based on this information I want to calculate the forecasted stock for the coming weeks. If we move to the next week, I want to use my week 6 actuals as input for the forecast for the coming weeks again. Since I only have actuals for the last week I need to use some sort of a circle reference in my opinion in order to make this one running. However, I didn't find the solution yet.  

I tried now the following formula:

Stock Forecast = CALCULATE(Total Stock],DATEADD([Date],-7,DAY))-[Forecasted Sales]+'[Production Plan]

This forecast will be accurate for 1 week ahead but the weeks after it will show me only the change in stock since the [Total Stock] only has actuals for last week. 

Does anyone know how I can make this a running forecast with updated actuals? 

Many thanks in advance. 

[v-frfei-msft]

Hi @iwjvanaerschot,

Could you please share your sample data and excepted result to me, if you don't have confidential data? Please upload your file to One Drive and share the link here.

Regards,

Frank

[iwjvanaerschot]

Hi Frank,

Thanks for your response. Let me give you a very small data set example of my problem: 

Normally the Forecasted Sales, Forecasted Production and Actual Stock are located in three different files and connected in the Data Model. But for the sake of explanation I just included them in 1 table now. 

So I have a forecast plan for sales and production. The fourth column, is the impact on my stock: + Forecasted Production - Forecasted Sales. The calculation I am looking for is a running forecast in the stock column. We are now in week 6 so I want my actual stock of week 5 + forecast impact of week 6. Once we reach week 7, I want to automatically use the week 6 stock as input and calculate the forecasted stock for the coming x weeks based on that volume. Actual stock week 6 + Impact week 7 = Forecasted Stock week 7.  Forecasted stock week 7 + Impact week  8 = Forecasted stock week 8 and so forth.. 

Hopefully you can help me out! 

Ian