# Showing Difference Between Sales And Budgets To Date – Forecasting In Power BI

The topic that I’m going to discuss in this tutorial was part of a full-hour workshop on budgeting and forecasting in Power BI during an Enterprise DNA Learning Summit. You may watch the full video of this tutorial at the bottom of this blog.

This tutorial focuses on how we calculate and highlight the differences between cumulative sales and cumulative budgets up to a certain date only.

Firstly, I’ll show you the problem that I believe a lot of you may have experienced when it comes to budgeting and forecasting in Power BI. Then, I’ll show you the DAX formula to calculate the difference and the visualization of the result.

## Cumulative Sales vs Cumulative Budgets To Date

Let’s look at this chart presenting the cumulative sales and budgets. You’ll see that we have a cumulative total for our budgets, which is illustrated by the dark blue line, while the cumulative total for our actuals is in the light blue colored line.

In this visualization, we only wanted to show a cumulative total up to the last sale date or up to today, for example. Now we want to work out the difference between these two numbers (sales and budgets).

It’s not as easy as just subtracting cumulative sales from our cumulative budgets, because cumulative budgets mean calculating up the entire year. It’s not right because we want to see it up to today or the last sale date only. And that’s what this table below shows.

If you take a closer look at this table, you’ll see that we have a breakdown per city.

Now let’s look at the DAX formula I created to get this insight.

## DAX Calculation To Work Out The Difference

I called the measure Sales vs Budgets To Date. In the first part of this calculation, I used variables (VAR) and the CALCULATE function to work out the very last sale date.

The CALCULATE function goes and finds the very last day (with MAX) in the OrderDate column, which is in the Sales table.  But first of all, remove any context from the Sales table and that’s what the ALL function does.

The second part of the formula is key to this calculation. It requires subtracting budgets from sales, but we’re doing it in a different context, which is what the CALCULATE function enables us to do.  This context is going to be adjusted by the LastSaleDate, as we only want to calculate up to the last sale date.

The ALLSELECTED Dates will create a list of dates in the current context. In this case, it’s 2018 because I have this Page Level Filters down to 2018. And so it’s only looks at 2018 budgets.

Then, it’s going to look through every single date, and if that date is less than the LastSaleDate, we will include it in this calculation.

And that’s how you get the difference to forecast up to the last sales date. This is very similar to calculating a cumulative total, but in this case, we’re restricting the cumulative budgets result.

You’ll see in the table the result, which the the difference, -350, 597.93. And we have a breakdown of the amount of each individual city, showing the sales versus budgets to date.

## Conclusion

This is a relatively simple technique, but a very useful one for your own reports on budgeting and forecasting in Power BI. It’s just a matter of understanding the DAX functions used especially CALCULATE, and how you can change the context within CALCULATE using FILTER.

I hope you can make use of this technique efficiently in your business and be able to see the significant insights from your analysis.

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