Revenue Forecast Technique for Project Data – Power BI Insights With DAX

No comments

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.

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.

revenue forecast

It’s calculating the Estimated Daily Revenue.

revenue forecast

But if the Project Start Date is less than or equal to the MAX Date, it will equate to TRUE.

revenue forecast

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.

revenue forecast

The MAX Date and MIN Date equate to the current date for every single day.

revenue forecast

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.

revenue forecast

With this technique, you’re able to identify your cash flow and manage resources more effectively.

Conclusion

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

membership banner 3

***** 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

***** Related Course Modules *****
Budgeting & Forecasting
Business Analytics Series
Mastering DAX Calculations

***** Related Support Forum Posts *****
Project Revenue Forecasting
Maintain Forecast DAX Calculation In Different Context
Visual Has Exceeded Available Resources

For more revenue forecast support queries to review see here….

This image has an empty alt attribute; its file name is enterprise-dna-events-1-1.png

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.