- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Revenue forecast from actual sales

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Tuna123

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2017
05:45 AM

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.

1 ACCEPTED SOLUTION

Accepted Solutions

erik_tarnvik

Established Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2017
06:34 AM

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!

4 REPLIES 4

erik_tarnvik

Established Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2017
06:34 AM

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!

Ashish_Mathur

Super User

Re: Revenue forecast from actual sales

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2017
08:18 PM

Tuna123

Frequent Visitor

Re: Revenue forecast from actual sales

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2017
11:25 PM

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

Established Member

Re: Revenue forecast from actual sales

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-18-2017
05:53 AM

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 :-).