Calculate Difference from Previous Month in Excel Power Query
Question details
The user needs to calculate the monthly volume changes by customer and lane by comparing current row data with the previous month's value.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking month-over-month volume changes for different customers and lanes within a large dataset.
- Observed behavior
- Requires a method to match each row with the corresponding previous-month record, subtract the previous volume from the current volume, and handle cases where no prior month exists.
Ensure your dataset has a consistent date format (e.g., standardizing all dates to the first day of their respective months) so that current and previous month records can match accurately.
Use a Self-Merge in Power Query
Calculate the prior month's date for each row and merge the table with itself to subtract the previous volume.
This approach natively uses Power Query by calculating a lookup date and joining the query to itself based on Customer, Lane, and that specific prior month date.
Add a custom column to convert all transaction dates to the start of the month using the formula: Date.StartOfMonth([Date]).
Add another custom column to calculate the previous month's date using the formula: Date.AddMonths([StartOfMonthDate], -1).
Go to Home > Merge Queries. Select the same table from the second dropdown to perform a self-merge. Hold CTRL to select Customer, Lane, and the Prior Month Date in the top table, and match them with Customer, Lane, and the StartOfMonthDate in the bottom table.
Expand the newly merged column to extract the previous month's Volume. Add a final custom column to calculate the difference: [Volume] - [Previous Volume], handling null values with a conditional statement if necessary.

Calculate Using DAX in the Data Model
Use the Excel Data Model (Power Pivot) with DAX formulas for a more efficient and dynamic calculation on large datasets.
Use a Standard PivotTable
The simplest method to display month-over-month differences without advanced queries or formulas.
Calculate Monthly Differences Easily in WPS Office
You don't need complex Power Query steps to analyze data. WPS Spreadsheet allows you to calculate month-over-month changes quickly using its robust, built-in PivotTable features.
- 1. Open Your Data: Launch WPS Spreadsheet and open your data file containing the customer, lane, date, and volume.
- 2. Insert a PivotTable: Select your data range, navigate to the Insert tab, and click PivotTable.
- 3. Set Up Rows and Columns: Drag the Customer and Lane fields into the Rows area, and the Date (Month) field into the Columns area.
- 4. Apply Difference Calculation: Drag the Volume field into the Values area, right-click the values, select 'Show Values As' > 'Difference From', and select the previous month as your base.

Frequently Asked Questions
Why does my Power Query self-merge return null for the difference?
This happens when there is no matching record for the previous month for a specific customer and lane. You can wrap your subtraction formula in a conditional statement or use the 'try...otherwise' clause to replace nulls with zero or a custom indicator.
Can I calculate the month-over-month difference without using Power Query?
Yes. The easiest non-query alternative is using a standard PivotTable. By placing your date field in columns and right-clicking your value field to select 'Show Values As > Difference From', you can achieve the same result instantly.
What date format is best for matching previous months in Power Query?
It is highly recommended to standardize all date entries to the first day of their respective months using the Date.StartOfMonth function. This ensures that the current month's lookup date accurately matches the previous month's record during the merge.




