Calculating changes across time periods is an essential data analysis skill. Learn how to use DAX formulas to calculate Month over Month change in Power BI. We’ll cover the easiest way to setup the calculation, how to present it as a number or a percentage and cover pre-requisites like creating a date table to avoid errors.
Calculating changes between months is one of the most common tasks in data visualization. Whenever you publish a report that has the current month or YTD performance, report viewers next question is usually how does this compare to the previous period?
Let’s look at how to setup the calculations and the pre-requisites!
Table of Contents
Why Calculating Month-over-Month Changes is Important
Calculating Month over Month or “MoM” changes is a crucial skill for any business intelligence developer or person creating a report. Months are typically the smallest unit that accounting departments report finalized financial statements in which makes it the smallest increment that many trends will be analyzed within a business.
By examining how key metrics fluctuate from one month to the next, companies can identify trends, make data-backed decisions, and allocate resources more efficiently. Presenting the change as a number along with percentage helps report readers identify the total change along with the relative scale of change from one month to the next.
Before getting into the calculation there are some key pre-requisites to be aware of.
Ensure That You Are Working with a Date Data Type
Before diving into MoM calculations, it’s essential to make sure your date fields are in the correct “Date” data type, and numeric data type. It’s an often-overlooked step and will either cause DAX formula errors or calculates to return incorrect numbers in your visuals.
The fastest way to determine if a column is defined with a Date Data Type is to look at the data panel on the right side of the Power BI report building screen.
Dates will have a Calendar icon to the left of the field name. Numeric columns will have a Sigma or Sum sign to the left. If there is nothing to the left of it, this is typically an indication of a text data type.
For Month over Month calculations to work correctly columns must be defined appropriately as a date or number.
If you need to update a data type, Right Click on any of the column names on the data panel and select edit quity.
This launches the Power Query Editor.
From the Power Query Editor, you can right click on a column name, and select Change Type, and select the desired data type.
We put together an in-depth guide to Power BI data types to explain in detail what each data type does and additional considerations you should be aware of.
For now, we only need to make sure that numbers are defined as numbers and dates are defined as dates.
How to Set Up a Date Table in Power BI
Another common pre-requisite for calculating month over month changes in Power BI is a Date Table. Setting up a Date Table is crucial for performing any time-based calculations. Most datasets aren’t fully sequential and often skip dates. The Date Table filles in the gap and enables Power BI to perform accurate calculations across time periods.
To create a new date table in your Power BI report follow these steps:
1.) Create a New Table – go to Table Tools and Select New Table. A formula bar will appear down below.
2.) Name the New Table and use the CALENDARAUTO DAX Formula.
The CALENDARAUTO formula is the fastest way to create a date table in Power BI. The number in the parentheses at the end of the formula is the year end month. In most cases this is 12 but could be different if you are creating a report for a business with a different fiscal year end.
3.) Build a relationship between the new date and the existing date field in your data
Click on the relationship editor tab on the left side of the screen. Then drag the Date Column from the existing dataset over the top of the New Date Table. This tells Power BI that there is a relationship between the two fields.
When you’re done, you will want to flip back to the Report View, right click on the new date column and ensure that it’s marked as a date table.
There are a lot of additional considerations when working with dates and date tables in Power BI. Our comprehensive guide to Power BI date tables provides greater detail than what we go into here.
For now this should be enough to get you started.
Calculating Month Over Month Changes in Power BI
When we setup DAX formulas that contain multiple measures or multiple parts we like to break the calculations into smaller pieces. Power BI lets you create formulas, give them names, and then reference those names in future formulas. It’s a much better system than having to create on massively long formula in Excel and it helps keep the logic modular for future re-use and is easier to read.
First, let’s calculate our Prior Month Sales.
Using a DAX Filter Formula for Prior Month
To calculate MoM changes, you first need to create a measure that calculates the amount for the prior month. Power BI does this using two formulas you may be unfamiliar with, CALCULATE and PREVOIUSMONTH.
CALCULATE lets you modify the context in which data is aggregated. In other words, you can use CALCULATE to tell Power BI to ignore the normal filtering syntax or normal grouping and define whatever you want it to be for a specific field or measure.
PREVIOUSMONTH is Power BI short hand for a FILTER that’s already setup to be the previous month. You could alternatively define specific dates, but in many cases Microsoft has done some of the heavy lifting for us.
To Setup a Prior Month Calculation in Power BI use this formula:
Prior Month Sales = CALCULATE(Sum('Cookie Sales'[Total Sales]), PREVIOUSMONTH(Dates[Date]))This is what it looks like when entered into Power BI.
You will need to modify the formula to reference your specific tables and columns. The result is a new Prior Month Sales measure that we can add to visuals that will only display the prior month amount.
Once added to a visual, our prior month sales calculation will look like this:
Note: It’s important to know that in addition to PREVIOUSMONTH you can use the same formula and adjust it for PREVIOUSDAY, PREVIOUSQUARTER, or PREVIOUSYEAR to ca
Calculate Month-over-Month Percentage Change
Next, you can calculate the percentage change between this month and the previous month using another DAX formula. The Month over Month percentage change is calculated by taking the difference of the prior month from the current month and dividing it by the prior month.
This is where having a new Prior Month Sales measure makes things easy.
The formula to calculate month over month change is:
Month over Month Change = (SUM('Cookie Sales'[Total Sales]) - [Prior Month Sales])/[Prior Month Sales]Our resulting calculation will be in the form of a decimal, you can either adjust the formatting with the % symbol on the Measure Tools section of the Ribbon or you can multiply the formula x 100 to get a whole number without the % symbol.
When added to our visual, it will look like the following:
Note: When Power BI does not have a prior month value available it will display the amount as Infinity. This is because it attempts to calculate division with a zero. To avoid this from showing you can wrap the DAX formula in an IF statement to return a blank or a number. Alternatively, you can filter your visual to not show the earliest month in your dataset.
Calculate Month-over-Month Change with Quick Measures
It’s important to understand the essential DAX formulas for period over period changes along with the CALCULATE and FILTER functions (PREVIOUSMONTH works the same as a DAX FILTER function).
If you’re looking to get things done faster and avoid formulas all together, you can drag and drop fields into the Power BI Quick Measure builder and have it generate formulas for you.
To create a Quick Measure follow these steps.
1.) Launch the Quick Measure Editor
Click on Quick Measure under the Measure Tools section of the Power BI Ribbon. Then select the calculation Month-over-month-change in the dropdown box.
From here, Power BI provides you with a drag and drop template where you can add field names to the Quick Measure editor and have Power BI generate the formula for you.
All you have to do is click the check box and the results even come out in a percentage!
Calculate Month-over-Month Change with Power BI Copilot
Another option to create formulas in Power BI is to leverage Artificial Intelligence in the form of Power BI Copilot. Copilot is Microsoft’s implementation of a ChatGPT style interface to ask Power BI to generate a DAX formula in plain English and let it write the formula on your behalf.
Suggestions with Copilot is also available under the Quick Measures bar. If you do not have the option available in your version of Power BI check out our guide to enabling Copilot.
In its current form, Copilot doesn’t work all of the time and can struggle with data models with many different fields, but when it does work it’s like magic!
Conclusion
Understanding how to calculate Month over Month (MoM) changes in Power BI can provide your business users with valuable insights that are essential for data-driven decision-making. By leveraging DAX formulas, Quick Measures, and Power BI Copilot, you can efficiently understand your business trends and adapt to market changes swiftly.
If you find that there are errors or incorrect calculations, check to make sure that a date table is properly setup and assigned. You will also want to check data types to make sure that numbers are numbers and dates are dates.
Some issues are to be expected, such as when no prior period information is available or when Power BI is asked to divide by zero. You can use an IF statement to wrap your DAX formulas to make them more dynamic, filter out, or find and replace unwanted values in your visuals or in Power Query.