# Predict Future Profitability Within Power BI DAX Functions

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

## Temporal Scale Using Calculated Columns In Power BI

Every so often we’ll be needing the availability of a custom visual of a bar chart or line chart that...

## First N Business Days Revisited – A DAX Coding Language Solution

Let's take another look at the problem discussed in this tutorial, which dynamically compared the first...

## Junk Dimension: What Is It And Why It’s Anything But Junk

Today, I want to talk about a data modeling concept called junk dimension. From its name, you'd think...

## Power BI Slope Chart: An Overview

In this tutorial, we'll be looking at a not-so-common custom visual called the Power BI slope chart....

## Create Power BI Reports With These Techniques & Examples

For today’s blog, I want to walk through a couple of amazing reports and share some of the tips I have...

## How Much Does Power BI Cost – Decoding Pricing Options

Are you considering using Microsoft's Power BI for your business intelligence needs? If yes, then it's...

## Create a Perpetually Updated Practice Dataset in Power BI

I'm going to show you how to keep your practice dataset updated every time you do a refresh. You may...

## DAX Calculation Groups To Avoid Unpivoting Columns

In this tutorial, I’ll demonstrate how you can create 2 split percentages in a donut chart using DAX...

## Expression Builder: Applying Conditional Formatting On Paginated Reports

In this tutorial, you'll learn how to apply conditional formatting in your paginated reports. The...

## Dynamic Segmentation With Dynamic Parameters – Advanced Power BI & DAX Technique

I'm going to talk about dynamic segmentation using dynamic parameters because I've seen members ask...

## Appending Several Sheets In Excel To Power BI

This tutorial will discuss about how to import and open an Excel file with multiple sheets to one Power...

## Pros & Cons of Quick Measures In Power BI

Today, I want to do an overview of quick measures and show you how they have evolved over time. I'll...