1. Forecasting in Power BI: DAX
Hi there! In this session, we will be discussion a DAX-forward approach to forecasting.
2. Context and importance
You may ask yourself, "Power BI has built-in capabilities to forecast; why would I need to use DAX?" While these built-in capabilities are great, they are limited in how we can use them. The configuration options are short in scope and mainly focus on the visualization side of things while ignoring optimizations that can be made to the model.
Luckily, there are many other alternatives in the market. Programming languages like Python and R present sophisticated libraries and packages that can forecast even the most complex data. Even Excel has some pretty powerful built-in forecasting methods. Both of these technologies are the standards for forecasting analysis. While they are outside the scope of this course, we can approximate much of their analysis using DAX.
3. DAX forecasting basic principles
Even though DAX cannot leverage external libraries that make forecasting easy, we can still approximate the behavior these more sophisticated models use. Generally, they follow the same three steps:
First, we want to identify the last data point we have. This will represent the point from which we can calculate a forecast.
Second, we need to calculate a number representing the data trend over our time frame. In our examples, we will be calculating a geometric mean that is common for financial analyses.
Thirdly, using the geometric mean, we project data into the future based on the last available data point.
This will provide at least a good estimate of the overall trend of the data.
4. CAGR
In the following exercises, we will be using metric known as CAGR, or the Compound Annual Growth Rate. This is a very common metric used in the financial industry and will model a stock's behavior into the future. Because CAGR is a geometric mean, this is used for when the data roughly represents an exponential curve.
To start calculating CAGR, we begin by finding our dataset's first and last values. We will use these to calculate a ratio, which effectively represents the overall magnitude of change for the dataset. We then take this value and take it to an exponent equal to one over the number of periods of data we have (like the number of years of data we have). This will take that overall change and put it into relative growth on a per-period basis. Think of this as the "slope" of our exponential curve. Finally, we subtract one to put our per period relative change into a percentage, which is more broadly applicable.
Note that while we have defined this formula for annual change, it can be easily translated into other time intervals, such as monthly or daily.
5. Using CAGR to forecast
CAGR provides a good indication of how a dataset has grown over time, but it is merely a measure of what has happened.
To forecast a future value with CAGR, we begin again with the last value of data we have. The last value of data serves as our baseline to base all future assumptions.
From there, we multiply that value by a factor of one plus CAGR, which represents the amount the data value will change, taken to an exponent n, which represents the number of periods (in this case, years) into the future we want to forecast. Essentially, we are taking our last value and assuming that it will continue to grow as the dataset has over its history.
Tying it all together, hindcasting is a great technique to test how accurate this model is for your data.
6. Let's practice!
With that knowledge behind us, let's start creating forecasts for our stock data using DAX!