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

## Microsoft Flows: Editing And Tracking In Power Automate

Once you understand how a Flow Diagram looks like, it will be easier to make some changes to it. In...

## Creating Power BI Reports Effectively & Avoiding Hidden Pitfalls

In this tutorial, I will discuss four hidden pitfalls in Power BI that can wreck your data model and/or...

## Power BI Page Navigation Buttons

The Power BI page navigation buttons play a critical role when it comes to storytelling. An organized...

## Power BI Report Examples And Best Practices – Part 1

In today's post, I'd like to present some Power BI report examples and best practices. In my own Power...

## Power BI Python Tutorial: How To Translate Texts

This blog will demonstrate how to perform language or text translation using Python and pipe it over...

## Measure In Power BI: Optimization Tips And Techniques

In this tutorial, youâ€™ll learn how to optimize a measure in Power BI. Optimizing measures in your...

## How To Use Power Query Row And Column Selection

This tutorial will discuss how to use selection and projection inside the Power Query Editor. Selection...

## Use Tabular Editor To Create Calculation Groups In Power BI

Today's blog post will give you an introduction to calculation groups. I'll try to answer four basic...

## Effective Data Storytelling: Asking The Right Questions

To ensure that we have a good story to tell, effective data storytelling by asking the right questions...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Turning Calendar Type Layout Into Tabular Format In Power BI Using Query Editor

Analyzing the data that we have can be difficult if it is not correctly arranged. In this tutorial, I...

## Create A Lookup Table From Subtotals: Optimization In Power BI

In this tutorial, weâ€™ll go through a technique that will create a lookup table from subtotals using the...