Pivot Table Issues

Create a Month-over-Month Revenue Variance Measure in Excel Power Pivot

Ayan MasoodAyan Masood Nov 18, 2026 869 views

Question details

Create a DAX measure that compares current-month revenue with the previous month for customers and products.

Create a Month-over-Month Revenue Variance Measure in Excel Power Pivot
Product
Microsoft Excel Power Pivot
Device & OS
not provided
Scenario
A user wants to analyze monthly revenue changes by customer and product in a Power Pivot PivotTable.
Observed behavior
The PivotTable should show current revenue, previous-month revenue, and the difference for each selected customer and product.
Before you start

Check that your Excel edition includes Power Pivot and that your revenue data has a date column, a numeric revenue column, and customer and product fields. Use date values without time components so they can match a calendar table.

Solution 1Recommended

Build a calendar table and create DAX variance measures

Use a related calendar table and DAX time intelligence to calculate revenue changes while retaining customer and product filters.

The calendar table must contain one unique row per day, with no gaps, and cover the revenue dates and the previous months you want to compare. Use complete calendar years where possible.

The formulas below assume the tables are named Revenue and Calendar, with columns named Revenue[Amount] and Calendar[Date]. Replace these names with the names in your data model.

1
Enable Power Pivot

In Excel, open File > Options > Add-ins. Select COM Add-ins in the Manage box, click Go, and enable Microsoft Power Pivot for Excel if it is available.

2
Add the tables to the data model

Create an Excel table named Calendar with a Date column containing every date in the required range. Select each table and use Power Pivot > Add to Data Model to add both Calendar and your Revenue table.

3
Set up the date relationship

Open Power Pivot > Manage, switch to Diagram View, and create a relationship from Calendar[Date] to the date column in Revenue. In the Power Pivot window, select Calendar and use Design > Mark as Date Table, then choose Calendar[Date].

4
Create the revenue measures

In Excel, select Power Pivot > Measures > New Measure and create these measures one at a time: Total Revenue := SUM(Revenue[Amount]); Previous Month Revenue := CALCULATE([Total Revenue], DATEADD('Calendar'[Date], -1, MONTH)); Revenue Variance := VAR PreviousRevenue = [Previous Month Revenue] RETURN IF(ISBLANK(PreviousRevenue), BLANK(), [Total Revenue] - PreviousRevenue).

5
Build the PivotTable

Insert a PivotTable using the workbook's data model. Put a year and month field from Calendar in Columns, customer and product fields in Rows or Filters, and the three measures in Values.

Build a calendar table and create DAX variance measures
Handling missing previous-month revenue: The variance formula returns a blank when the previous-month measure is blank, rather than treating missing prior revenue as zero. Include a year with each month to avoid combining the same month across different years.
Free Microsoft Office alternative

Explore monthly revenue data with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Office formats, making it easy to move everyday spreadsheet work. It does not replace Excel Power Pivot or fix its DAX measures; check advanced data-model features before migrating a workbook.

Review and organize monthly revenue data in a familiar spreadsheet interfaceOpen and edit common Excel formats, including XLSX and XLSUse a free, lightweight office suite for everyday spreadsheet workMove routine spreadsheet tasks from Microsoft Office with familiar tools
microsoft office alternative - wps office

Frequently Asked Questions

Why do I need a separate calendar table for the DAX measure?

A calendar table provides a continuous set of dates for DATEADD to shift the PivotTable's date context. Relate Calendar[Date] to the revenue date column and mark Calendar as the date table in Power Pivot.

Why is my previous-month revenue blank?

The prior month may have no matching revenue, or it may fall outside the calendar table. Check the date range, the Calendar-to-Revenue relationship, and whether both date columns contain matching dates without time components.

Will the measure respect customer and product filters?

Yes. When the customer and product fields filter the Revenue table through the data model, CALCULATE shifts the date context while retaining those filters.

How can I show a percentage change instead of a revenue difference?

Create another measure using DIVIDE([Total Revenue] - [Previous Month Revenue], [Previous Month Revenue]) and format it as a percentage. DIVIDE returns blank by default when the previous-month value is zero or blank.

Can WPS Office run the Power Pivot DAX measure?

WPS Spreadsheets can open common Excel files, but it is not a replacement for Excel Power Pivot. Use an Excel edition with Power Pivot to create and calculate this DAX measure, and verify advanced model features before moving the workbook.