# 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

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Power BI Report Example For An Optical Dataset

A lot of you may know that we have an ongoing Power BI Challenge. One of our recent Power BI report...

## AVERAGEX: Calculating Average Per Day In Power BI

Here I'm going to show you how to use the function AVERAGEX with DAX in Power BI. You may watch the...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...

## Format Data In Power BI: Addressing Irregular Data Formats

In today's blog post, we'll discuss Problem of the Week #6. I'll show you how to format data in Power...

## Calculating A Rolling Average In Power BI Using DAX

In this tutorial, Iâ€™m going to show you how to calculate a dynamic Rolling Average in Power BI using...

## Paginated Report In Power BI: An Introduction

In this tutorial, youâ€™ll learn about paginated reports in Power BI. A paginated report is another name...

## Creating Measure Tables & Subfolders In Power BI

For today's blog, I'll cover measure tables and subfolders. We're going to set up measure tables, and...

## Ultimate Guide To Multiple IF Statements In Power BI

If you are looking to create more complex logic, then using multiple if statements in Power BI is a...

## Highlight Highest & Lowest Values Using Quick Measure In Power BI

Quick Measures Pro is a powerful external tool to streamline data analysis and visualization in Power...

## Power BI Desktop Update: The Charticulator Visual

Today, I would like to go over something that I found out by scrolling through Twitter. What I have...

## How To Build a Portfolio And Showcase Your Data Projects to Employers

In todayâ€™s data-driven world, the ability to work with data has become an essential skill. Whether...

## New vs Existing Customers – Advanced Analytics In Power BI

If youâ€™re an online retailer or a high frequency sales operation, then understanding your customer...