Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# How to Compare Current Sales to Previous Best Month in Power BI

by | 9:00 am EDT | March 31, 2020 | DAX, Power BI

In this tutorial, we’re going to learn more about the advanced time intelligence feature in Power BI to compare current sales to the previous best month. You may watch the full video of this tutorial at the bottom of this blog.

This is actually a unique question that was raised at the Enterprise DNA Support Forum. And as soon as I heard it, I thought wow! We can actually do this in Power BI.

This is a very unique piece of analysis that will give you more insight into what leads to successful outcomes within your organization.

As we already know, successful businesses often compare their revenues for this month to their best month throughout their organization’s history. This is because it’s very important to understand what specific factors were at play and also how these factors interact to create strong results in the revenue.

It’s truly amazing how we can get this insight about advanced time intelligence in Power BI.

## Comparing Total Sales To Previous Best Month

Here in this table, you can see what should be our end product. This is how it’s going to look like when we try to compare current sales to the previous best month in Power BI.

As we can see in the table, we should be able to have a calculation that’ll allow us to continually evaluate the current month in every month prior to that. When we can see what is the highest amount up until that point, we can consider that as the highest sales so far.

In the table, the first result we have under the Highest Previous Sales Month column is in February. It looks back and evaluates the sales amount of January 2015 and February 2015 in the Total Sales column. Then, it returns the highest number which is 1,024,700.

As we move down the table, we can see that in July 2015, the result is now higher than the previous one. That is because between the previous months up until July 2015, the highest total sales was 1,049,952.

A pretty cool insight, right? This comparison can totally give us an indication of how well the business is performing. Now, let’s get down to the advanced calculations.

## Calculating The Best Previous Month

Now, the challenge here is how to create a calculation that could really compare the sales effectively. As soon as we can calculate the numbers in the Highest Previous Sales Month column, we can easily compute the percentage in the Comparison vs Best Month column.

First, this is our formula.

To calculate the total sales, we need to totally change the context of the calculation and rank the sales from highest to lowest.

To return the highest amount, we need to use the TOPN formula.

With that, we can change the context from a ranking perspective. But we also need to specify only one row in the table, so you need to enter 1

Now, the result of that row is going to be determined by the logic that we place within it. To achieve that, we should use the FILTER function, with SUMMARIZE function inside of it.

By using the mentioned formula, we are returning a table for every single Month & Year,

and also the MonthnYear column as well.

In my data table, the MonthnYear column is a numeric field.

Meanwhile, the Month & Year column is actually a text field.

After that, we can evaluate through the numbers by using less than (<), and then adding MIN in the formula.

The formula returns the corresponding month and year index. But because it’s within a filter, we’re going to rate through every single month and year to create a dynamic table.

To finish off our TOPN formula, we need to rank every month within the virtual table based on a particular measure. Since we only want to return the top sales up to that point, we need to put that measure and enter Total Sales.

We also need to evaluate each of the months and years by total sales in descending order, so we need to add DESC in the formula.

When we look back on the table, we can see that June 2015 has the new highest number after 1, 024, 700. So that’s our highest previous sales month.

## Working Out The Comparison

Lastly, I created a simple logic for comparison with the best month.

To compare current sales to previous best month, I used a simple logic with the DIVIDE function. Subtract the Highest Previous Sales Mth from our Total Sales and then divide the difference by the Highest Previous Sales Mth.

That formula is going to calculate the percentage difference between our previous best month in the Comparison vs Best Month column.

As we can see by now, using DAX calculations in Power BI can bring about very unique insights. It is a great technique to really get ahead of your business. Personally, I love how powerful this analysis is in Power BI.

## Conclusion

Sometimes, it’s not only worthwhile to analyze historic months, quarters, or years. Many times, it might actually be helpful to focus on that one dynamic month where the best performance was achieved.

We can efficiently complete these calculations using Power BI to compare current sales to the previous best month. Certainly, there are many ways to combine various different DAX functions and logic within the formulas. Additionally, we can learn to effectively use CALCULATE and FILTER functions together.

Cheers,

Sam

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## How to Move Files in SharePoint: Top 4 Methods Explained

Struggling to move Files in SharePoint? Don't worry, we've all been there. Knowing how to move files...

## How to Return List in Python: 3 Top Methods Explained

The return statement plays a pivotal role in Python. It’s what lets your functions communicate with the...

## How to Create P Value in Excel? A Step-by-Step Guide

Ready to create a P value in Excel? Want to learn how to quickly and efficiently do it? Read on. To...

## How to Strikethrough Text in Excel? Easy Guide + Examples

Want to strikethrough some text in your Excel document but don’t know how? Don’t worry, it's easy, read...

## How To Calculate Z-Scores in Excel? User Guide with Examples

Ready to calculate a z-score in Excel? Awesome. Because we are going to show how to do it quickly and...

## Top 20+ Data Visualization Interview Questions Explained

So, you’re applying for a data visualization or data analytics job? We get it, job interviews can be...

## Top 10 Free Datasets for Statistics Projects

Are you looking for a way to explore and analyze real-world data to enhance your understanding of...

## How to Calculate Standard Error in Excel? Top 3 Methods

The standard error is a key statistical tool that allows you to assess the accuracy and reliability of...

## How to Delete a SharePoint Site: 4 Best Methods Explained

Ready to delete your SharePoint site? It really is as easy as 1,2,3 and 4! To delete a SharePoint site:...