Data scientists typically are the ones who handle predictive analysis by using their extensive knowledge in data science, programming, and historical data to determine future outcomes.
What if I tell you that you do not need to be a wizard in data science for you to incorporate your essential forecasts into your reports? Thanks to Power BI, you only need historical data with associate dates to create a Power BI line chart with the prediction for the period you specify.
Now before you go, no, this is not the topic of this article; I know that it’s a feature that has existed for a long time; this is just an introduction for the new power bi users. What we will tackle in this article is a problem that most of you have probably faced at least once in your career; at least I did! Just last week, I was supposed to create a chart with two lines, with one that does include the prediction, but the line chart prediction is limited to only one line per chart.
Once you have more than one line, the feature is no longer usable. Thankfully, there is a workaround that is pretty easy to apply! For beginners, I recommend you go through the first section named Initial Setup. For other readers, you can skip this section and go to the next.
Initial Setup: Data model and measures
Before we start, this is the data model I’m using. I have one fact table named Sales; it includes the foreign keys for my dimensions (Products, Locations, Customers, Sales People, etc.) and the price and quantity ordered for each purchase.
I created measures for total sales, total cost, total profits, and profit margin, which you can find here:
⦁ Total Sales = SUMX(Sales, Sales[Price] * Sales[Quantity])
⦁ Total Costs = SUMX(Sales, Sales[Quantity] * RELATED (Products[Product Cost]))
⦁ Total Profits = [Total Sales] – [Total Costs]
⦁ Profit Margin = DIVIDE([Total Profits], [Total Sales], 0)
I also created one measure for the sake of this example so that we can have two-line charts.
⦁ Profit Margin Goal = 0.4
Creating a Power BI line chart with a forecast
To create a line chart with the forecast:
⦁ Add a line chart to your report, drag the dates to the x-axis, and your measurements to the y-axis.
⦁ In the Analysis area, activate the forecast and choose your units, forecast length, seasonality, and confidence interval.
“Seasonality works best if the chart has at least four times more values than the data cycle. For example, if you set the seasonality to 52, you would want to have at least four times 52, or 208, values in the time series axis of your chart.”
In my case, I chose 10 points with automatic seasonality and a 95% confidence interval, which gave me a chart that looks like this:
Tip Time!
To create a line chart with the forecasting feature and other lines, you need to have two charts overlaid on top of each other.
Consequently, both charts need to end at the same period and have the same y-axis. In the end, we will group them in the selection tab to be able to move them together as we want.
⦁ Let us start with an empty line chart and make its position to (0,0).
⦁ Add to it in the y-axis both your measures (for me, it’s profit margin and profit margin goal).
⦁ For my x-axis, I will create a custom one with my date values + the length of my prediction (in my case, 10).
Then I will set a maximum date based on this measure, and I will get something like this:
⦁ Remove the title and any legend you have because it will impact the alignment later.
⦁ Copy-paste the resulting chart, put it on top of the first chart, remove the profit margin goal from the y-axis, then go to the analysis area and activate the forecast again. Abracadabra!
⦁ Finally, go to the selection tab and group both charts together.
You now have a line chart with more than one line and a forecast. Awesome, right?
I hope this trick will help you one day, and if it does, please let us know, and don’t forget to share your thoughts if you have another trick for the same problem. See you soon!