Analyzing past revenue is interesting and informative, but sometimes you want to look into the future. Is it realistic to expect my revenue to increase next year, and by how much? Estimating revenue is essential for setting budgets, sales targets, and supplier bonuses. Traditionally, we’ve done this manually, sometimes with the help of an Excel model based on real-world data. With a large volume of data in a data platform and accessible computing power, we want to make this process more accurate, more frequent, and compatible with Power BI.
A wholesaler with an SAP ERP environment approached us to ask if Ensior could forecast their revenue 12 months in advance. This customer uses a Power BI Premium environment. The forecasting method needed to fit within this framework. Therefore, we evaluated both the built-in Power BI forecasting method and various models written in Python.
Data from 2019 through 2022 was available in the database. To test how accurately a model predicts revenue 12 months in advance, we built the models using data from 2019 through 2021 and then tested them on the 2022 data. The graph below shows this data on a monthly basis.
Power BI Predictive Model
In Power BI, it is possible to use the forecast feature with a line chart; we will refer to this as the Power BI forecast model. The Power BI forecast model uses an exponential smoothing model to generate the forecast. We have configured the Power BI forecast model with a seasonality of 12 months. We did not include the last 12 months in the model, and the forecast covers the next 12 months. This allows us to compare actual revenue with the forecasted revenue. When we compare the forecast to the actual revenue, we see that the forecast reasonably follows the revenue pattern. This model could already provide a good indication of how revenue might develop.
The advantage of a predictive model in Power BI is that it can be implemented quickly and requires no programming knowledge. This visualization is interactive and displays tooltips. You can also export the underlying data, including the forecast values. A major drawback, however, is that you cannot visualize the forecast values in any other way. Another drawback is that model optimization happens behind the scenes, and you have little control over it. These drawbacks led us to explore Python models.
Python in Power BI Desktop
We wanted to compare whether we could build a predictive model in Python that provides more accurate predictions than Power BI. To do this, we implemented several models: Linear Regression, Extreme Gradient Boosting, SARIMA, and ETS. To arrive at a set of models to test, it’s important to have a good understanding of your data and statistics. We first analyzed the data and conducted a literature review to determine which models would potentially work well on this dataset. We compared the models by calculating a number of metrics based on the 2022 forecast. The graph below shows the different models along with their metrics. Based on these results, we decided to proceed with the ETS model.
With a local installation of Python, you can create a Python visualization in Power BI Desktop. To do this, select the Python visualization and drag the fields you need into the visualization. Power BI then generates a default Python script that creates a pandas dataframe from those fields. You use this dataframe in the rest of your code. You can paste your Python script below the default script to generate the visualization.
The advantage of a predictive model in a Python visualization is that it allows you to create advanced visualizations, since you can program everything yourself. You can apply all kinds of models and then visualize them however you like. Once you’ve written the Python code, implementing it in Power BI is a breeze. The downside of this visualization is that you can’t make changes in Power BI online. Tooltips and interactions aren’t possible with this visualization. If you export the underlying data, you’ll only see the input data—not the values of the predictions.
Python in SQL Server Machine Learning Services
Due to the aforementioned drawback—that the data remains within Power BI’s Python framework—we chose to run Python separately and capture the data periodically during the nightly SAP ERP load process. To do this, a connection between Python and SQL Server is required. We then added the objects to the existing model so that we have full control over combining the forecast data with actual values.
The advantage of a predictive model in Python and SQL Server is that you can visualize and combine your data in Power BI in various ways. You can also use more complex models than the standard Power BI predictive model. The downside of this method is that it takes some time to establish the connection between Python and SQL Server and then get your Python script up and running there.
Conclusion
In this blog post, we’ve shown you three ways to present the output of a predictive model in Power BI. Which method works best for you depends on your knowledge and your needs. Hopefully, this blog post will help you make a good choice.
Using the chosen methodology, we have made the existing forecast significantly more reliable and periodic with less effort. A rolling forecast is now also available. This offers significant benefits for the organization. Incidentally, we’ve used a different model to forecast revenue at the customer level. While this model does a much better job of predicting revenue per customer than the previous system, there is still significant room for statistical improvement.
