Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Predict Future Profitability Within Power BI DAX Functions

by | 9:00 am EST | November 12, 2019 | DAX

In this blog post, I will showcase some of the analytical work I enjoy doing the most… and that is working on future predictions using Power BI DAX functions. You may watch the full video of this tutorial at the bottom of this blog.

I am always fascinated with trying to understand what could happen in the future.

I’ll show you a technique around how you can almost predict future profitability. What’s great about this technique is that it can be used in a lot of different ways. It could help predict future transactions, future demand, future revenues, future cost, etc.

A lot of reporting is done based on historical information. So if you can build your models and analysis to enable some type of prediction of future results, this is where you’ll drive significant value for your stakeholders who will then view and take action on the great analysis you’re doing.

We will dive into many DAX functions and how by combining a few of these, you can extract predictive results that can showcase information in an effective and dynamic way inside of Power BI.

## Predictive Analysis Using Power BI & DAX Functions

We are going to work through an example of how you can predict profitability for future time periods based on nuance.

We can use the information on what we have historically achieved to predict what we might achieve in the future. I have brought in a number of different factors indirectly by visualizing time intelligence functions.

In reality, there’s probably a couple of ways you can do this and there’s a number of scenarios and factors you might want to incorporate in these calculations.

Well, you can certainly do it using the same techniques that I’m going to use here, but maybe just slightly adjust them to get your desired results.

## Total Profits

The first thing we will do is to work out what our Total Profits are for the current month. We will use the formula Total Sales minus Total Costs. Pretty straightforward, right?

The next step is to create our prediction. Let’s look a closer look at our visualization here. The blue bars indicate the predicted results which we will arrive at by the end of this tutorial.

To do this, we are going to use time intelligence calculations based on how a business has historically performed from equal time periods far into the past but also factoring in what’s happening in the near term as well.

There could be some near-term cyclical factors, political factors, or socio-economic factors that are going to impact the results. So we’re going to factor them all in, and this is how you can actually do it.

## Long Term Impact

### Profits One Year Ago

For the long-term impact, we are going to use the CALCULATE and the DATEADD functions. So let’s go in and retrieve the exact same month’s profits from the year before using this formula:

### Profits Two Years Ago

We also want to go back to the profits from 2 years ago and see things from a monthly perspective.

Why are we doing this? Let’s say for instance that we are currently in December, so we need to factor in the profits from last December and the December before that.

This will give us the expected profitability for this month on average.

## Short Term Impact

There are short term factors that could impact performance so we also have to factor them in the insight.

It is no good to just look back last year and the year before that and say, “That’s enough for our prediction.” We have to somehow factor in near-term impact as well.

So what we will do is jump back to what was our profit from last month, and what was our profit two months ago by using these two formulas:

## Profit Prediction

The last thing we need to do to achieve our Profit Prediction is to bring in all of the results.

Let’s add the profits from last year, the profits from two years ago, the profits from last month, and the profits from two months ago, and divide the amount by 4.

That’s going to give us an average or a prediction on how our sales might go.

What’s so powerful is that we can now use this particular technique to come up with visualizations.

We can also visualize them together with the rest of our model.

Here’s our profit prediction versus what we actually made from a particular month. We’re looking at it from a state perspective (left) and product context (right).

Obviously, this is not perfect and there will be holes that you would be able to detect. But ultimately, this can serve as your guide and will showcase you how to do it.

With any prediction, there can be many nuances. These are things that you don’t have any control of and you don’t know when they are going to appear.

In reality, it is very hard to build this into a model. So you deal with the information as best as you can.

How You Can Predict Profitability In The Future Using Power BI
Check Whether Revenue Growth Is Profitable – Analysis In Power BI w/DAX
Calculating Percent Profit Margins Using DAX in Power BI

## Conclusion

In this blog post, I wanted to showcase a combination of time intelligence functions and bring them all together to create some sort of prediction. In this case, we’ve looked at profitability for future time periods.

We have used the CALCULATE function, the DATEADD function, and time intelligence functions.

The real quality insight comes from combining all of these techniques together — and that’s what I want you to always achieve in your analysis using Power BI and DAX formulas. I hope you can implement this one in your own environment.

Time Intelligence Calculations

All the best

Sam

## How To Get Your Dataset’s Top N In Power BI

In today’s blog, we will walk you through the process of using Quick Measures Pro to create a custom...

## Power BI Custom Sort Using DAX

Many people believe that we cannot perform Power BI custom sort using DAX, but that is not true. DAX...

## The Best Data Type For A Calendar Table In Power BI

Using a calendar table in Power BI allows you to filter your reports by time intelligence...

## The CALCULATE DAX Function: Issues & Solutions

In today’s blog, we will explore why you should not use the CALCULATE DAX function to obtain Average...

## CROSSJOIN DAX Function: Server Timings & Query Plan

In this tutorial, you’ll learn how the CROSSJOIN function works in DAX using the Server Timings pane...

## RANKX Deep Dive: A Power BI DAX Function

Today, we will dive deep into RANKX, a scalar DAX function in Power BI that allows you to return the...

## Removing A Hidden Date Table In Power BI To Improve Performance

In this tutorial, you’ll learn how to remove a hidden date table in Power BI to reduce RAM usage and...

## Data Cache: What It Is And How It Helps To Optimize Queries

In this tutorial, you'll learn what a data cache is and why it's important. A data cache stores bits of...

## Time Intelligence In DAX: How To Dynamically Select Starting Period

In this blog post, we will deal with some troublesome issues in time intelligence, particularly those...

## Get Power BI Previous Week Values Using DAX & Power Query

Retrieving previous period values in Power BI is a common task, but retrieving previous week values...

## The Ultimate DAX Guide For Beginners

The third pillar in Power BI development is DAX calculations. This tutorial contains a thorough DAX...

## DAX And Power Query | Creating Self-Sorting Columns

Following a recent DAX pop quiz that I prepared and posted on LinkedIn, one of the people who responded...