Forecast accuracy

[epaez]

Hello Community, thank you.

I have a linear series of product sales, month to month from the years 2015 to 2019, by product, customer

I'm using the Power BI Analitycs Forecast option to add the 2020 Forecast to the chart.

I see it correctly, but I would like to test the accuracy of this forecast:

The way to test it is to take the data from 2015,2016,2017,2018 and do the forecast for 2019, I'm already done, and I even have a graph with 2015,2016,2017,2018+forecast 2019 and another parallel with the real 2015.2016,2017,2018,2019 and I can see the comparison graphically of the year 2019, but I want to calculate the accuracy, that is to do a division (Real / Forecast)

any idea how to do that?

I don't know how to store this 2019 forecast in a table so that we can compare with the real of 2019.

Thank you.

Thank you

[v-yingjl · Accepted solution]

Hi @epaez ,

Basically you can export data from your line chart visual which includes forecast value like in 2019, here are the steps that you can try:

1. Export data in the line chart including 2019 forecast as a .csv file

2. Import the .csv file and transform data like only keep the date 2019 and the forecast value column in power query, close and apply it.

3. Create a relationship between the new table and the initial table based on the date field.

4. Create a measure to calculate the accuracy

Forecast accuracy = 
DIVIDE(SELECTEDVALUE('Sales Table'[Product sales]), SELECTEDVALUE('Forecast sales by 2019'[forecastValue]))​

5. Use a table visual to show the comparison

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@epaez , You can take advantage of Python integration
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-scripts
https://en.blog.businessdecision.com/how-to-use-the-python-integrator-in-powerbi/
https://towardsdatascience.com/using-python-in-power-bi-ee95a6b71443
https://powerbi.microsoft.com/en-us/blog/python-visualizations-in-power-bi-service/

https://powerbi.microsoft.com/en-us/blog/python-visualizations-in-power-bi-service/
https://www.ahaapps.com/microsoft-power-bi-service-with-python-visualizations/

[Greg_Deckler]

@epaez - Ah, you want to regression test the forecast to the actual numbers. Hmm, there is not any great solution for that necessarily but what about 2 charts? One you exclude the 2020 data and have the forecast. The other you include the 2020 data and no forecast. 

You could then use show as table (ellipses menu on chart) or possibly export on your forecast chart to get the forecast values and then you could compare those to your real values. See Page 2, Table 2 of the attached PBIX