How to Use Previous Month in Power BI

Need to compare this month’s performance against last month in Power BI? It's a fundamental analysis for almost any business report, whether you're tracking sales, marketing leads, or website traffic. Fortunately, Power BI’s formula language, DAX, provides several excellent tools for exactly this type of time intelligence calculation. This article will walk you through the most effective methods, from simple one-liners to more flexible formulas, to calculate and visualize your previous month's data.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why You Need a Dedicated Calendar Table

Before writing a single DAX formula, there's one critical step: you must have a proper calendar table. Trying to perform time intelligence calculations on the date column within your main data table (like a 'Sales' or 'Orders' table) will lead to errors and unreliable results. Why? Because your data table might have gaps in its dates - no sales on a Sunday, for example. Time intelligence functions require a stable, unbroken sequence of dates to work correctly.

A dedicated calendar table, or date dimension, is a separate table containing a continuous list of every day for the period you want to analyze.

Creating and Configuring Your Calendar Table

Creating a calendar table is surprisingly simple. You can use DAX functions like CALENDAR or CALENDARAUTO.

• CALENDARAUTO(): This function scans all date columns in your model and automatically creates a calendar that spans from the earliest year to the latest year it finds. It's great for getting started quickly.
• CALENDAR(): This function gives you more control, allowing you to specify a start and end date for your calendar table.

Here’s an example DAX formula to create a new calculated table. Go to the "Table view" in Power BI, select "New Table" from the ribbon, and paste this in:

DimDate = 
ADDCOLUMNS (
    CALENDAR (DATE(2022, 1, 1), DATE(2025, 12, 31)),
    "Year", YEAR ( [Date] ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "Month Num", MONTH ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" )
)

This code not only creates a table with every single day from January 1, 2022, to December 31, 2025, but it also uses ADDCOLUMNS to create helpful columns for Year, Quarter, Month Number, and Month Name that you can use for filtering and slicing.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Mark as Date Table and Create a Relationship

Once your table is created, you have two final setup steps:

1. Mark as Date Table: In the Model view or Table view, right-click on your new calendar table and select "Mark as date table." A dialog box will appear asking you to choose the primary date column - select the default "Date" column. This tells Power BI that this is your master table for all time-based calculations.
2. Create a Relationship: Switch to the "Model view." Click and drag the 'Date' column from your calendar table onto the date column in your fact table (e.g., the 'OrderDate' column in your 'Sales' table). A one-to-many relationship (1..*) should form, with the "1" side on your calendar table.

With this foundation in place, you're ready to perform powerful time comparisons.

Method 1: The Easy Way with PREVIOUSMONTH

The simplest way to get the previous month’s figures is with the PREVIOUSMONTH function. It does exactly what its name implies: returns a table containing all dates from the month before the current date context.

Like all time intelligence functions, you'll use it inside a CALCULATE function. CALCULATE is the supercharged engine of DAX, it modifies the "filter context," which means it can change how a calculation is performed for different filters (like changing the date range).

Step-by-Step Example

Let's say you have a basic measure for total revenue:

Total Revenue = SUM(Sales[RevenueAmount])

To calculate the revenue for the prior month, you would create a new measure like this:

Revenue Last Month = 
CALCULATE(
    [Total Revenue], 
    PREVIOUSMONTH('DimDate'[Date])
)

Here’s how it works:

• [Total Revenue] is the expression we want to evaluate.
• PREVIOUSMONTH('DimDate'[Date]) is the filter. For any date in the current context (say, a row in a table for October 2023), this function returns a set of all dates from September 2023.
• CALCULATE then computes the [Total Revenue] using that filter, effectively giving you September's revenue when displayed on the October row.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 2: Gaining Flexibility with DATEADD

While PREVIOUSMONTH is perfect for its specific job, you'll eventually need more flexibility. What if you want to compare against the same month last year? Or two quarters ago? This is where DATEADD shines.

DATEADD is a versatile time-shifting function. Its syntax is: DATEADD(<dates>, <number_of_intervals>, <interval>).

• <dates>: Your date column from the calendar table (e.g., 'DimDate'[Date]).
• <number_of_intervals>: A positive number to move forward in time or a negative number to go back. For "previous month," you'd use -1.
• <interval>: The period you want to shift by. Options include DAY, MONTH, QUARTER, and YEAR.

Example using DATEADD

To replicate our "previous month revenue" calculation, the DATEADD measure would look like this:

Revenue Last Month (DATEADD) = 
CALCULATE(
    [Total Revenue], 
    DATEADD('DimDate'[Date], -1, MONTH)
)

This formula achieves the same result as our PREVIOUSMONTH example but opens the door to far more comparisons just by changing the parameters:

• Same Month Last Year:

CALCULATE([Total Revenue], DATEADD('DimDate'[Date], -1, YEAR))

• Previous Quarter:

CALCULATE([Total Revenue], DATEADD('DimDate'[Date], -1, QUARTER))

• Revenue from Two Months Ago:

CALCULATE([Total Revenue], DATEADD('DimDate'[Date], -2, MONTH))

For this reason, many experienced Power BI developers prefer DATEADD as their default for time-based comparisons due to its superior flexibility.

Putting It All Together: Visualizing Your Comparison

Creating these measures is only half the battle, the real value comes from visualizing them. A common and effective approach is to use a matrix visual.

Let's create a report that shows the month-over-month change:

1. Drag a Matrix visual onto your report canvas.
2. From your 'DimDate' table, drag 'Year' and 'Month Name' onto the Rows field. Be sure to sort your 'Month Name' column by 'Month Num' to get the correct January-December order.
3. From your measures table, drag [Total Revenue] and [Revenue Last Month] into the Values field.

Now you'll have a table showing revenue for a given month right next to the previous month, making for an easy manual comparison.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Create a Month-over-Month % Change Measure

To make the report even more insightful, you can calculate the percentage change directly. Create a new measure with the following formula:

MoM Revenue % Change = 
DIVIDE(
    ([Total Revenue] - [Revenue Last Month]),
    [Revenue Last Month]
)

The DIVIDE function safely handles any potential division by zero errors (for months where there was no revenue in the previous month). After creating the measure, select it in the Fields pane and use the "Measure tools" in the ribbon to format it as a percentage.

Add this final measure to your matrix. Now your stakeholders can instantly see not just the raw numbers but the growth trajectory month by month.

Common Pitfalls and How to Avoid Them

If your previous month measures are returning blank or incorrect values, it's usually due to one of a few common setup issues.

• Problem: My 'Revenue Last Month' is always blank. Solution: This almost always points to a problem with your calendar table. Double-check that you've marked it as the official date table and that an active, one-to-many relationship exists between its date column and your data table's date column. A lack of continuous dates in your calendar can also cause this.
• Problem: The calculations are wrong for the first month in my data. Solution: This is expected behavior. If your data starts in January 2023, there is no "previous month" (December 2022) for the measure to calculate, so it will correctly return blank. Ensure your calendar table's date range fully covers your data's date range.
• Problem: The grand total on my matrix looks strange. Solution: The grand total row evaluates the DAX measure without the row-specific date filter. So, the "last month" for the grand total is often looking at the month before the earliest date in your whole table. It's usually best to focus on the individual row values rather than a grand total of a time-based comparison measure like this.

Final Thoughts

Calculating previous month values is a gateway to meaningful time intelligence analysis in Power BI. By setting up a proper calendar table, you can easily use DAX functions like PREVIOUSMONTH for quick results or DATEADD for more powerful and flexible comparisons. Adding a percentage change measure on top of that turns a simple report into a powerful analytical tool.

Building these DAX measures and wrestling with filter contexts can be complex and time-consuming. We believe getting these insights shouldn't require you to be a data engineer. With Graphed, we connect directly to your data sources, then you can just ask in plain English, "Show me my revenue for this month compared to last month as a bar chart" or "What's my month-over-month revenue growth for the last six months?" We instantly create the live dashboard for you, skipping the frustration of DAX syntax so you can get straight to the answers you need.