Data forecasting in Power BI. Effective business analytics and predictions

Data forecasting in Power BI – what it is and how can it help your business?

Data forecasting is the process of using historical data sets to predict future events, trends and results. The technique is used in statistics, data analytics and machine learning in order to understand the patterns and trends hidden in available data sets and employ them to predict future values. 

Cost and resource optimisation

For the purpose of boostingorganisations need to analyse trends in many dimensions. Data forecasting will allow you to improve your management of resources such as raw materials, workforce or capital; accurate predictions can be used to flexibly adapt your asset allocation to expected business activity level for extra cost optimisation. A similar effect can be achieved in logistics and stock management. Operations, too, can be adjusted as a function of predicted activity.

Marketing

Analysing historical data and forecasting future consumer trends will enable you to plan and implement your marketing campaigns more effectively. You will easily adapt your strategies to market demands, customise your portfolio and better segment your target groups.

Risk identification

Data forecasting also allows companies to identify potential risks and quickly respond to rapidly evolving market conditions to minimise the negative impact of unpredictable external circumstances.

Sales support

In addition, sales data forecasting enables a more strategic approach to resource management, advertising and process optimisation. Predicting future sales results helps your company understand the market, adapt to changing trends and take more informed business decisions. 

As you can see, modelling future values can be of key importance to your ultimate success or failure. It really makes sense to support this process with advanced technologies such as, for instance, Microsoft Power BI (which will allow you to tap the analytical potential of a business intelligence solution, but also AI and machine learning).

Built-in data prediction algorithms in Power BI

Power BI is not just a real-time reporting tool. It is also a comprehensive system that taps the full potential of business data to create new insights and improve decision-making. Its versatility really makes it stand out from other business intelligence solutions. 

Power BI offers not just advanced analytics, but also important features that allow future outcomes to be estimated: from classical time series forecasting to AI tools that can predict not just quantitative but also qualitative (descriptive) data values.

Below, I will present a few examples of sales data analytics to demonstrate the potential of Power BI in that area. 

Power BI. Time series forecasting

Time series forecasting in Power BI employs the exponential smoothing method. Exponential smoothing is a technique of time series processing that reduces its variance by using the weighted moving average of earlier values, where the weighting factors decrease exponentially as a function of time.  

In other words, exponential smoothing relies on a weighted average of previous observations, where the most recent observations are assigned a higher weighting factor; the weighting factor decreases exponentially as the observations age.

This is based on the assumption that the latest observations provide more information about future trends. Exponential smoothing is an effective tool to reduce model noise, detect trends and visualise diverse data. 

The figure below explains the idea of exponential smoothing:

Depending on whether your forecasting data set is regular and cyclical or does not display any seasonal changes, Power BI will automatically select one of the two available exponential smoothing algorithms. For seasonal data, it will use ETS AAA (exponential smoothing with additive error, additive trend and additive seasonality), while non-seasonal data will be processed with ETS AAN (exponential smoothing with additive error, additive trend and non-additive seasonality). 

Seasonal ETS AAA algorithm

ETS AAA accounts for seasonality in its time series modelling equation. Commonly known as the Holt-Winters algorithm, it was upgraded by Power BI to include a validation process for parameter optimisation and state vector correction at the end of training window when your data is noisy.  

The Holt-Winters method selects the optimal smoothing parameters to minimise forecasting errors in the training window.

However, it is mainly focused on short-term forecasts, which may lead to inaccuracies when you want to get a longer horizon forecast. In order to improve long-term modelling, Power BI introduced a validation window, which contains the last few points of the training window.

Within the validation window, you do not need to adjust the state at each and every step; instead, you compute the sum of squares of prediction errors for the window as a whole. This has the effect of dampening variation and preserving a trend over a longer-term perspective. 

In ETS AAA, the state vector is calculated for the training window as a whole, but when the smoothing parameters are relatively high, the model can become sensitive to outliers, especially at later stages.

This may distort your forecasts, pulling the training fit towards these outliers. To prevent that from happening, the Power BI algorithm automatically tracks variations in the training state.

When it detects a large variation, it will adjust the trend in the time window to more closely match the overall trend of the time series and tweak the forecast values accordingly.   

Non-seasonal AAN algorithm

The other algorithm, ETS AAN, does not account for seasonality. It uses a simpler equation to model the time series, which includes only a term for additive trend and additive error. It assumes that data values increase or decrease in some way that can be described by a mathematical formula, but that the increase or decrease is not cyclical.

How to apply a Power BI forecasting model

Time series modelling in Power BI is like solving a business jigsaw puzzle. First, you choose the jigsaw puzzle as such, for example, a linear model. Second, you pick the puzzle pieces, such as time period and historical data, e.g. sales data. Once you have created a database, you can proceed to the key element: forecasting. 

To this end, the following parameters need to be defined:

• Units – such as e.g. year, month, day, hour, minute or point;
• Forecasting period – the period you want to include in the forecast. The parameter takes numerical values (e.g. for a six-month forecast, you enter 6);
• Ignore last – used whenever you want to ignore a period. Some datasets may contain incomplete data for the last x months. You can use this feature to tell the model that the data for a given time period is incomplete and should be ignored;
• Seasonality – seasonality occurs whenever there is any predictable, cyclical change in any time series. In Power BI, you can set the value 12 for annual seasonality, 6 for semi-annual seasonality and 4 for quarterly seasonality;
• Confidence interval – the confidence interval represents the probability that actual values will fall within a given range. For instance, if the confidence interval is 95%, 95% is the probability that the actual value will fall in your range.

When all these pieces are added to the puzzle, you will be able to see the full picture and predict future values, as shown in the figure below: 

Power BI is known for its visually attractive reports; it allows you to adjust the visualisation to your preferences and dashboard design (you can pick from a variety of colours, fonts and other visual elements). In addition, you can supplement your visualisation with trends, mean, median and outlier lines, and even use an anomaly detection feature. 

Information provided by a Key Influencers visualisation

Power BI has a lot of other features to support companies in modelling future events. A visualisation known as Key Influencers presents the key elements that impact your metric of interest.

The tool relies on impact analysis algorithms, such as decision trees or regression algorithms, to evaluate which variables are most strongly correlated with a given target value. This feature relies on artificial intelligence. 

In order to build the visualisation, all you need to do is import your data set. Power BI will automatically analyse the data, identify key influencers based on analysed patterns in available variables and generate visual explanations.

Top Segments view

Another available view is Top Segments, based on associative analysis algorithms, such as a priori analysis. They will search your data sets for elements that frequently appear together.

The algorithms will then weight different segments to assess their impact on the outcome. This allows you to identify the key data segments with the greatest influence on your results. 

The Key Influencers feature is not a classic example of data forecasting, but it may visualise very important information to help you to predict future data.

It enables interactive analytics (you can click individual elements for explanations to see how they were computed), which allows you to understand the relationship between variables in greater depth. It can also be easily integrated with other dashboard elements.

Integrating Power BI with Python and R to reinforce forecasting algorithms

The potential of Power BI is not limited to its built-in features; forecasting analytics can be improved with Python and R, the main two programming languages used in data science and forecasting. With its wide range of libraries, such as NumPy, Pandas, scikit-learn, ARIMA and many others, Python is widely used for data analytics and machine learning. R, exceptional for its wealth of math packages, such as forecast or timeSeries, is also a basic language for statistical data analysis, forecasting, modelling and visualisation.

Using external forecasting models in Power BI: the added value

The potential of languages such as Python and R allows you to create brand new Power BI models ideally matched to the features of the data in the domain.

As a result, you gain a lot of flexibility in terms of using various scientific forecasting methods, such as linear regression, logistic regression, decision trees, neural networks or machine-learning algorithms such as Random Forest, Gradient Boosting, etc.

In addition, hyperparameter tuning allows you to tweak model performance for optimal results. You can use techniques such as grid search or Bayesian optimisation 

Another advantage is that you can freely choose from tried-and-tested algorithm testing techniques. You can rely on different metrics such as Mean Squared Error or R-squared.   

Thanks to integration with Python and R, you don’t need to be dependent only on built-in Power BI algorithms. You can create matched, dedicated models to further improve forecasting accuracy. 

Effective model application in Power BI

In practice, first you need to create your forecasting model as a Python script. Ideally, you should work on it outside Power BI, in any code editor like Visual Studio Code or PyCharm, or Jupyter Notebook, which is a popular solution in data science. Such tools will make it easier for you to train and test your predictive model and adjust the parameters to your needs. 

Before you choose a methodology, you usually need to divide your data into two sets: a training set, on which the model will be trained, and a testing set, which will allow you to evaluate its efficacy.

The model is thus adjusted and adapted to the training data set. Once you are happy with how the algorithm works, you can save your code and apply it to your initial, historical data set in Power BI. The latter will be automatically fed into the data forecasting model you developed in Python to generate a new data set with sales predictions. 

Finally, you can create a report based on the new data set, including diagrams and other components to visualise your results. Remember that Python and R can be used not only for computation. You can also use them to create your own visualisations! 

Forecasting and Power BI. Conclusions

Data analytics and forecasting are becoming necessary elements in the development strategy of any organisation that wants to succeed in the age of Big Data. They allow you to take more informed decisions, improve operational effectiveness and maintain flexibility in a dynamic market environment.

With its wealth of built-in features and potential for integration with external tools, Power BI is a solution that brings companies closer to all these goals.