In this tutorial, I’m going to show you a real-world insight that enables you to utilize project or opportunities data to create a Revenue Forecast in Power BI. You may watch the full video of this tutorial at the bottom of this blog.
This technique is extremely relevant to CRM systems. This can also be used on your captured information like Sales and Projects, and also on potential revenue forecasts.
This is also very useful to consultation companies that have been awarded projects. They know what the Projects are, their start and end dates, and their values. And let’s say the project lasts for two years and generates a $500,000 profit, what would the estimated revenue from a daily perspective be?
In a business, you need to pay people for their work. Thus, you need to know when the money would come in or at least have a Revenue Forecast.
Ways To Get The Revenue Forecast
You can see that the Cumulative Total is set up as the Revenue Forecast.
Let’s have a look at the demo data.
There’s an Estimated Revenue of the project. And every single project has a Project Number or, in this case, an Opportunity Number. The Project Worth, Estimated Start Date and Estimated End Date are shown. The Estimated Project Duration is set to 12 months.
Now, let’s say you want to know the statistics on the 12th of August 2019 and then be able to allocate the Revenue across every single day that the Project is live.
The key to getting the answer is to first solve this in the model.
You want to be able to work out when the Project starts and ends and then allocate the amounts across every single day within that time frame.
To do this with a DAX format, you need to create inactive relationships within the model. Having active relationships will create a filter context in the report which will stop you from getting the right answer.
The other thing you can do is to work out the difference between the End and Start to know the Project Duration. And then, solve the Total Estimated Revenue divided by the Days to get the Daily Revenue.
Solving Estimated Revenue
Let’s look at the formula.
This is called the Events and Progress Pattern or Formula Combination.
It’s calculating the Estimated Daily Revenue.
But if the Project Start Date is less than or equal to the MAX Date, it will equate to TRUE.
For those that are TRUE, it will evaluate through all their Project End Dates and see if it’s greater than or equal to the MIN Date.
The MAX Date and MIN Date equate to the current date for every single day.
So, to understand if a Project is live, all you need to do is to find out if the Start Date is less than the Current Date, and if the End Date is greater than the Current Date. Then, you need to calculate the Estimated Daily Revenue.
This logic is the same for every single date in the table.
The Estimate Revenue formula gives us the running total of all these daily revenue amounts, and then accumulates them for every day.
If you make a visualization containing this information, it becomes a chart showing Revenue Forecast through time.
With this technique, you’re able to identify your cash flow and manage resources more effectively.
Conclusion
***** Related Links *****
Create A New Table In Power BI: How To Implement Budgets & Forecasts Automatically Using DAX
Use Power BI Analytics To Check If Revenue Growth Is Profitable
Calculating Reverse Cumulative or Reverse Running Total In Power BI
This is a good real-world example of handling multiple dates.
Once you start to understand this technique and the concept of inactive and active relationships, you can make your analysis and reports come together in Power BI.
Understanding how to utilize this formula technique will enable you to use this analysis within your organization to make better decisions.
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
[youtube https://www.youtube.com/watch?v=t2JTOE76q0E?rel=0&w=784&h=441]
