In this tutorial I run through some great scenario analysis techniques and demonstrate how you can combine several of them in Power BI. I’m talking about forecasting product demand. You may watch the full video of this tutorial at the bottom of this blog.
Power BI is an amazing tool for data analysis when you can implement techniques like this one.
Take a situation where you want to analyze future product demands. Having the ability to set up your models in Power BI so that you can run several scenarios at once can provide you with insights that can take your business to the next level.
Also, being able to incorporate some ‘What if‘ parameters and then run scenario-type analysis enables you to forecast or predict what you might need to do in the future to extract the right amount of revenue or optimal amount of profit.
This tutorial will show you how you can estimate future product demand in Power BI using DAX.
To do this, we need to have a sort of benchmark to determine how much we will sell. And then, we might need to layer on top of that some factors that could change up that demand.
In my view, the best benchmark to layout a forward forecast or estimated forward-looking amounts is looking at what we have traditionally sold. Now, let’s dive into how we can do that using What If parameters and DAX in Power BI.
Estimating Demand Using What If
To utilize the What If parameter, we simply click on New Parameter in the modeling ribbon.
So in this demonstration, I have created what I call a Demand Factor, which is a table.
With this table, we can change the demand and see what happens to our demand expectations.
Here we have a visual that represents what we expect for the demand to go based on the demand factor that we’ve put into our model.
In our model, we see that this Demand Factor is just a supporting table. We can just extract the numbers out of here and then feed them into our calculations.
Now let’s have a look at this forecasting demand analysis from a DAX perspective.
Estimating Demand Using DAX
When forecasting Demand, we need to project forward some historical sales and incorporate this Demand Factor. This is easy to achieve because of the the amazing time intelligence functions in Power BI.
First, we calculate our Sales Last Year (LY). What this formula is doing is simply looking back in time at the exact day before.
However, our actual results are quite busy. So I’ve done some smoothing to get a clear insight and I strongly recommend this from a visual perspective.
To do this, we layer on the moving averages pattern or moving average technique. Here’s the calculation I made for this:
I did Estimated Sales first, which is equal to Sales Last Year multiplied by one plus the Demand Factor. Then I smoothed it by working out a moving average for that Estimated Sales that we’re calculating.
Looking at the chart, the results look far more appealing when we put the smoothing factor. And so we now can use the power of the data model and select the quarter and year to bring that in and out, forecasting demand quickly and easily.
We can also select them here as well and it’s going to change the visual.
It’s not going to show it as expected because this is only calculating the forward amounts.
***** Related Links *****
Forecasting Analysis Techniques In Power BI With DAX
Explore Forecasting Logic In Your Power BI Models Using DAX
Using SAMEPERIODLASTYEAR To Compare The Difference Between This Year & Last Year
Conclusion
So that’s how you can combine lots of techniques in forecasting demand using the power of the What If parameter and DAX in Power BI.
We’ve started off by creating a parameter table, and then created total sales as a core measure. We’ve moved to use time intelligence and incorporated our What If parameter. Then, we’ve smoothed it out and visualized it. That’s how we get the projection forward.
You have to understand the data, the DAX formula, the parameters, and the context to get this all working well.
You’ll find by learning this technique that there are many different ways that you can expand on it, and not just be restricted to analyzing or predicting product demand. For example, you can also explore changes to regional demand and/or store demand.
There are just so many ways of applying the concepts explained in this tutorial, so it is well worth spending the time to understand this well.
All the best,
Sam
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events
