How to forecast sales with Power BI

[hidenseek9]

Power BI community,

I am wondering how to create sales forecast dashboard with Power BI.

I know it is capable of it, but not sure how to achieve this.

I want to show the final output to be in a monthly calendar format as below.

First off, I have 3 different dataset.

1. Actual figures (Historical Data)
2. Current month data
3. Year to go data

If the current month is July, then 1 would be Jan-June, 2 would be July and 3 would be Aug-Dec.

Please find below as the Power BI with sample data loaded in.

Sample Data

Here is what I would like to do.

1. Using historical/actual data, I would like to forecast the volume for all the months in 3. Year to go data. (In the data I loaded, I have dummy volume numbers, but would be nice to forecast volume using 12 months seasonality)
   • Using Historical dataset in Sample Data.pbix, I would like Power BI to forecast the volume in Aug-Dec'18
2. With the volume forecasted in number 1, I would like Power BI to calculate Gross Sales to Net Net Sales. (If you can show me a way to calculate First Commissions for example, then I will do the same process for the rest of the categories)
   • With the volume forecasted in 1, would like Power BI to forecast First Commissions. I will do the same for the rest of categories such as Second commissions.
3. In current month, I have a data from Gross Sales to Invoiced Sales for all the days. With the trend in the month, I would like Power BI to calculate the volume to be achieved by the end of the month along with Gross Sales
   • With Current Month dataset in Sample Data.pbix, I would like Power BI to forecast the volume and Gross Sales to be achieved in the month. 
4. With all the data forecasted from step 1-3 above, I would like to put all of them together in a calendar view as the screenshot above.

I understand that there are a lot of questions, but once someone can teach me how to do forecasting in Power BI,

I should be able to figure out the rest, maybe.

Appreciate your support in advance.

Many thanks,

H

[stretcharm · Accepted solution]

Powerbi can do forecast graphs but not provide the numbers.

https://databear.com/forecasting-in-power-bi/

https://www.mssqltips.com/sqlservertip/5085/data-forecasting-and-analytics-with-power-bi-desktop/

R maybe a better as it has very powerful features and could provide a data set of forecasts.

Here are a couple of links that may get you started.

https://skillsmatter.com/skillscasts/9075-powerbi-meetup

http://community.powerbi.com/t5/R-Script-Showcase/Forecasting/m-p/58485

https://www.r-bloggers.com/basic-forecasting/

Hope this helps

[hidenseek9]

@stretcharm

Perfect!

Let me try and get back to you!

Many thanks,

H