# Compare Cumulative Information Over Different Months In Power BI

Showcasing cumulative results in Power BI is such a powerful way to visualize your data over time, especially if you are trying to show the difference in performance from one time period to another. You may watch the full video of this tutorial at the bottom of this blog.

When you try to present this at a daily or even monthly level, identifying the trend isn’t always so obvious. When you place this into cumulative totals it becomes as clear as day.

The real power of DAX gets showcased when you start to combine formula patterns.

That’s exactly what we are going to do here. I’m going to show you how you can jump to create any time comparison and then quickly get those results into a cumulative total.

This enables you to layer one on top of the other and really effectively show the difference in performance trends across any time period.

Let’s go ahead and start working on our example.

## Creating The Core Measure

First of all, we’re going to create a simple measure. Let us create Total Sales which is going to be the SUM of the Total Revenue column.

Of course you can use any other measure. But just for the purpose of this example, we’re using Total Sales because it’s very simple which will make this lesson easy to understand.

Now that we have our Total Sales measure, let’s set up the table. So this is going to be a Total Sales table sorted by date.

Then we’re going to drag in our MonthInCalendar because we’re going to look at historical information. So let’s drag that in and turn it into a slicer.

Now, we will be able to select any of the months in this slicer.

After setting up our table, we’re going to need a time intelligence function.

## Using A Time Intelligence Function

Let us again take a quick look at our Total Sales table. What we have here are the total sales presented per month.

Now, what we need to do is to generate the calculation that will allow us to compare data from various months.

Here, I’m going to use my favorite time intelligence function which is the DATEADD function. It’s very versatile and flexible as I’m sure you’ll realize once you’ve become more familiar with it too.

So, we’re going to create a new measure which is Total Sales LM or last month. Then I’m going to add CALCULATE which changes the context of our calculation.

Then I’m going to use DATEADD and put in the Dates column.

Next, I’m going minus one and then go MONTH.

When you’re working on your on calculation, of course you can look at DAY, QUARTER, or YEAR. But here let’s use MONTH.

When we close off our formula, it’s going to look like this one below.

If we drag in this measure into our Total Sales table, you’ll see that the figures in the Total Sales LM column correspond to the figures the previous month in the Total Sales.

At this point, we can easily look at the figures from two months ago. We just copy and paste our formula and tweak the name of our new measure and then write minus two instead of minus one.

Now, if we drag that into our table, it will show the total sales two months ago.

And it’s also dynamic because of our slicer. Whatever month and year that we select in our slicer will be shown in our sales table.

This figure below, for example, shows the January 2016 figures in the Total Sales column and they’re compared with the Total Sales last month in the next column then two months ago in the rightmost column.

## Calculating Cumulative Information In Power BI

If we convert this table into a clustered column chart, you’ll notice that we won’t be able to generate any trends.

So, let us look at the data in a better way which is cumulatively. For this, we’re going to use the cumulative totals pattern.

Let’s create a new measure and call it Cumulative Sales, then use CALCULATE, and go Total Sales, then ALLSELECTED, and then add the entire table not just the dates column.

Next we’re gonna go Dates, less than or equal to MAX then Dates.

Now, we have a Cumulative Sales column which shows the Total Sales cumulatively.

As you can see, it starts off with 189, 636.80 which is the Total Sales on the first of June 2016. Then it adds the sales on the first and the second of June so we have 427,011.10, and so on and so forth.

Now, check this out. It’s really amazing how easy it is now to compare our Total Sales one month ago with our Total Sales two months ago cumulatively.

All we need to do is to copy and paste our Cumulative Sales formula and then just modify the name so that it says Cumulative Sales LM or last month. Then instead of Total Sales, we’ll select our Total Sales LM measure.

We’ll just repeat this process when we create our Cumulative Sales two months ago measure.

Then let’s drag them into our table.

Now, we can turn our table into a visualization.

## Turning The Table Into A Visualization

Let us first delete our intermediary calcs such as the Total Sales, Total Sales LM, and the Total Sales 2M Ago.

What we have left in our table now are the cumulative sales columns.

Next, let us turn this table into an area chart.

Now, we’ve got this trend analysis across all these months.

And it’s also dynamic. We can select any of the dates in our slicer and the data for that selection will be shown in our area chart.

I’m sure you find this technique really cool.

It’s very easy to set up because once you have your core measure and time intelligence pattern, you can easily create the cumulative total pattern.

And just like that we have created some fantastic analysis.

Showing Actual Results vs Targets Only To Last Sales Date In Power BI
Calculate A Reverse Cumulative Total In Power BI Using DAX
Cumulative Totals In Power BI Without Any Dates – Advanced DAX

## Conclusion

In this example we focused on monthly comparisons. We started off with our Total Sales, and then we compared it with the Total Sales last month and two months ago.

After this, we worked on our cumulative totals which enables us to come up with a very compelling visual that is also dynamic.

But think how far you could extend this. You could use this across virtually any time comparison and across any of your key metrics like sales, costs, profits etc.

The thing I like most about cumulative totals is the trend-identifying aspect.

And when you add in additional context from within your model, it really showcases where diversions in results started occurring. It’s really powerful stuff.

All the best with this one.

Sam

## Power BI Shape Map Visualization For Spatial Analysis

The Shape Map Visualization is probably my most favorite map visualization inside Power BI. I think it...

## Python Correlation: Guide In Creating Visuals

In today’s blog, we will walk through the process of visualizing Python correlation, and how to import...

## Microsoft Flow HTTP Trigger | A Power Automate Tutorial

A Microsoft Flow HTTP trigger allows users to trigger flows from third-party applications. In this...

## Data Visualization Tips For Your Power BI Reports

In today's blog post, I'm going to do another review of one of the submissions we had in the Power BI...

## How Will AI Affect Data Analysis in the Future

As the world becomes increasingly data-driven, the role of artificial intelligence (AI) in data...

## Showing Sales Growth In Power BI Reports

In this Power BI Showcase, we'll go through reports showing the Sales Growth analysis of a large...

## Power BI Visualization Technique: Learn How To Create Background Design Plates

Here's a Power BI visualization technique that you can utilize within your reports by using large...

## Creating Virtual Relationships In Power BI Using The TREATAS Function

The TREATAS function in DAX is one of the most interesting DAX formulas that you can utilise inside...

## Microsoft Power Query Tutorial On How To Fix Mixed Fixed Column Width Issues

Mudassir: For today, we have a very interesting problem to work with. The problem with this file is...

## MultiIndex In Pandas For Multi-level Or Hierarchical Data

MultiIndex in Pandas is a multi-level or hierarchical object that allows you to select more than...

## Power BI DAX ALL Function – How It Works

I want to give you a really quick introduction to the Power BI DAX ALL function. I find that there can...

## Sales Vs Budgets Insights – Extended Budget Allocation Formula

In this tutorial, I’m going to show you an extended version of the ultimate Budget Allocation...