In this unique example, I want to show you how to project a historical trend in Power BI and repeatedly project it forward. This particular example came from a question in the Enterprise DNA support forum. You may watch the full video of this tutorial at the bottom of this blog.
An Enterprise DNA member needed to carry out this analysis in the real world using historical information, which could be for example, quarterly or half-year results. They then had to take that older information and project them forward for some years.
This is an excellent example of how you can reuse time intelligence functions in many different ways.
You can also enable this logic to be filtered by a particular region, a specific store or for a particular customer set.
Visualizing Your Trend Analysis in Power BI
The visualization for your trend analysis is practically useful when you have a grouping, a quarter, or a sales period and you want to project or predict the results going forward.
This solution that I have discovered can be applied in a number of different ways. The current example may be about predicting a quarter, but you could project anything continuously going forward using a similar technique.
There’s a lot of interesting ways to apply a trend analysis inside your Power BI report. Now, take a look at this table below.
Firstly, you can see the columns for the specific dates of the year and its corresponding total sales. But, I also need to showcase what day it is in a particular quarter. That is why I created the Quarter Day column.
I’ve gathered that data from the Dates table. In this table, you can see the Quarter Day column. Let’s see how I worked this out.
Working Out The Quarter Day
A quarter day is a significant figure in business. It signifies the day that officially begins a three-month period of the year (a quarter). It is important to really work out this dynamic calculation of quarter day since I intend to project its trend every single quarter in every year.
So, in order to find out the quarter days, I used the formula below.
It’s just going to find any particular date, and then subtract it to the very start of quarter. Lastly, just add 1.
As I go down the Quarter Day column, you’ll see that it continues to go all the way up until about 92. And then, it goes back to 1 at the very first start of the next quarter.
Projecting A Particular Time Frame
Now that I have calculated the quarter day numbers, I need to decide on the particular time frame that I want to project forward. To do that, I need to use the formula below for the Quarterly Forecast.
First, I use the CALCULATE function for the Total Sales.
The FILTER function removes ALL the filter in the Dates table initially, and then re-applies them to a very specific time frame, which is 2015 for year, quarter 3 for quarter, and the very same day of a particular quarter.
The MIN function actually evaluates to the quarter day of every single day as we move through any month into the future and evaluates their correct quarter day.
So in any particular day in the future, it will always jump back to the 3rd quarter of 2015, on the same quarter day.
In this example here, I’m showing a very specific time frame instead of just repeating historic numbers. In the example visualization below, I’m actually comparing the quarterly forecast to the total sales.
The graph seems a bit busy so you can actually improve it a little bit. This is just one way to see it in a comparison perspective.
Aside from that, you can also use a moving average measure inside it. This can help users to calculate moving averages of a specific time frame, and then just continually project that out.
Moreover, you can predict anything out aside from sales and that’s where measure branching comes in. Actually, you can also add factors and even percentages for your particular results.
***** Related Links *****
How To Do Trend Analysis In Power BI Using DAX
Analysing Customer Trend Using DAX In Power BI
Using Moving Averages To Show Trends in Power BI
Conclusion
To sum up, all these trend analysis can be done very efficiently within Power BI when you set up your calculations correctly.
The great thing about this technique is that I feel this is such a real-world application in Power BI. Likewise, it’s also about how efficiently you can create this sort of logic using DAX formulas. Moreover, you can showcase it in dynamic ways when projecting your trends forward.
Good luck with learning this one.
Sam
