Revenue forecast from actual sales

[Tuna123]

Hi, 

I'm trying to create a monthly forecast table for products by using the last year's actuals as a strarting point. In my forecast table I have all the product/month-year combinations for the last year and the next five years. I've got estimated growth rates of sales in another table for each product. So I take, for example, last year's November's actual sales for each product, multiply that with the growth rate and get the next year's forecast. The problem is, that I need the forecasted figure to calculate the next year and same with the following and so on (for the next five years). How could I do this in one column?

I thought one way could be to look up the latest actual figure for each product/year-month combination and to multiply it with the growth rate 1-5 times depending how many years it is from the actual. But I couldn't come up with the function to find the sales value for each row. So the function should find the latest actual sales value that matches the month and product of each row.

[Ashish_Mathur]

Hi @Tuna123,

I solved a similar problem for someone yesterday.  See the file here.

[erik_tarnvik · Accepted solution]

Assuming you have the following tables:

Table Forecast:
Year	Month	Product	Actual
2017	Jan	A	10000
2017	Jan	B	20000
2017	Feb	A	15000
2017	Feb	B	30000
2018	Jan	A	
2018	Jan	B	
2018	Feb	A	
2018	Feb	B	
2019	Jan	A	
2019	Jan	B	
2019	Feb	A	
2019	Feb	B	

Table GrowthRates:
Product	Rate
A	1.05
B	1.1

There is a relationship on Product between the tables. Define a calculated column as follows.

Forecast = SUMX(
                FILTER(Forecast, Forecast[Year] = 2017 
                               && Forecast[Month] = EARLIER(Forecast[Month]) 
                               && Forecast[Product] = EARLIER(Forecast[Product])), 
                Forecast[Actual]) * RELATED(GrowthRates[Rate]) ^ (Forecast[Year] - 2017)

This also assumes that the growth rates are the same between years and only vary by product. Hope this helps!

[Tuna123]

Thank you @erik_tarnvik!

I just had to remove the year filter because some of the actuals are from last year (i.e. the forecast for end of this year currently) but it seems to work that way. Also, I used a similar method to find the year of actual sales that was used to forecast each row for the exponent. 

[erik_tarnvik]

Excellent @Tuna123! Please mark my post as a solution if you feel it is. Someone from Microsoft will remind you later if you don't :-).