In this blog post, I will showcase some of the analytical work I enjoy doing the most… and that is working on future predictions using Power BI DAX functions. You may watch the full video of this tutorial at the bottom of this blog.
I am always fascinated with trying to understand what could happen in the future.
I’ll show you a technique around how you can almost predict future profitability. What’s great about this technique is that it can be used in a lot of different ways. It could help predict future transactions, future demand, future revenues, future cost, etc.
A lot of reporting is done based on historical information. So if you can build your models and analysis to enable some type of prediction of future results, this is where you’ll drive significant value for your stakeholders who will then view and take action on the great analysis you’re doing.
We will dive into many DAX functions and how by combining a few of these, you can extract predictive results that can showcase information in an effective and dynamic way inside of Power BI.
Predictive Analysis Using Power BI & DAX Functions
We are going to work through an example of how you can predict profitability for future time periods based on nuance.
We can use the information on what we have historically achieved to predict what we might achieve in the future. I have brought in a number of different factors indirectly by visualizing time intelligence functions.
In reality, there’s probably a couple of ways you can do this and there’s a number of scenarios and factors you might want to incorporate in these calculations.
Well, you can certainly do it using the same techniques that I’m going to use here, but maybe just slightly adjust them to get your desired results.
Total Profits
The first thing we will do is to work out what our Total Profits are for the current month. We will use the formula Total Sales minus Total Costs. Pretty straightforward, right?
The next step is to create our prediction. Let’s look a closer look at our visualization here. The blue bars indicate the predicted results which we will arrive at by the end of this tutorial.
To do this, we are going to use time intelligence calculations based on how a business has historically performed from equal time periods far into the past but also factoring in what’s happening in the near term as well.
There could be some near-term cyclical factors, political factors, or socio-economic factors that are going to impact the results. So we’re going to factor them all in, and this is how you can actually do it.
Long Term Impact
Profits One Year Ago
For the long-term impact, we are going to use the CALCULATE and the DATEADD functions. So let’s go in and retrieve the exact same month’s profits from the year before using this formula:
Profits Two Years Ago
We also want to go back to the profits from 2 years ago and see things from a monthly perspective.
Why are we doing this? Let’s say for instance that we are currently in December, so we need to factor in the profits from last December and the December before that.
This will give us the expected profitability for this month on average.
Short Term Impact
There are short term factors that could impact performance so we also have to factor them in the insight.
It is no good to just look back last year and the year before that and say, “That’s enough for our prediction.” We have to somehow factor in near-term impact as well.
So what we will do is jump back to what was our profit from last month, and what was our profit two months ago by using these two formulas:
Profits One Month Ago
Profits Two Months Ago
Profit Prediction
The last thing we need to do to achieve our Profit Prediction is to bring in all of the results.
Let’s add the profits from last year, the profits from two years ago, the profits from last month, and the profits from two months ago, and divide the amount by 4.
That’s going to give us an average or a prediction on how our sales might go.
What’s so powerful is that we can now use this particular technique to come up with visualizations.
We can also visualize them together with the rest of our model.
Here’s our profit prediction versus what we actually made from a particular month. We’re looking at it from a state perspective (left) and product context (right).
Obviously, this is not perfect and there will be holes that you would be able to detect. But ultimately, this can serve as your guide and will showcase you how to do it.
With any prediction, there can be many nuances. These are things that you don’t have any control of and you don’t know when they are going to appear.
In reality, it is very hard to build this into a model. So you deal with the information as best as you can.
***** Related Links *****
How You Can Predict Profitability In The Future Using Power BI
Check Whether Revenue Growth Is Profitable – Analysis In Power BI w/DAX
Calculating Percent Profit Margins Using DAX in Power BI
Conclusion
In this blog post, I wanted to showcase a combination of time intelligence functions and bring them all together to create some sort of prediction. In this case, we’ve looked at profitability for future time periods.
We have used the CALCULATE function, the DATEADD function, and time intelligence functions.
The real quality insight comes from combining all of these techniques together — and that’s what I want you to always achieve in your analysis using Power BI and DAX formulas. I hope you can implement this one in your own environment.
To learn more about time intelligence functions, check out this comprehensive module at Enterprise DNA Online.
Time Intelligence Calculations
All the best
Sam
