Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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!
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!
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.
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 :-).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |