Create a Month-over-Month Revenue Variance Measure in Excel Power Pivot
Question details
Create a DAX measure that compares current-month revenue with the previous month for customers and products.

- 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.
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.
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.
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.
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.
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].
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).
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.

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.

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.




