Power Query Problems

Calculate Difference from Previous Month in Excel Power Query

Algirdas JasaitisAlgirdas Jasaitis Oct 23, 2026 873 views

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.

How to Calculate the Difference from the Previous Month in Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Standardize Dates

Add a custom column to convert all transaction dates to the start of the month using the formula: Date.StartOfMonth([Date]).

2
Add Prior Month Date Column

Add another custom column to calculate the previous month's date using the formula: Date.AddMonths([StartOfMonthDate], -1).

3
Merge Queries

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.

4
Expand and Calculate

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.

Use a Self-Merge in Power Query
Handling Nulls: If a customer or lane has no data for the previous month, the merged volume will be null. Ensure your calculation accounts for this by returning null or zero.
Analyze Data with WPS Spreadsheet

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. 1. Open Your Data: Launch WPS Spreadsheet and open your data file containing the customer, lane, date, and volume.
  2. 2. Insert a PivotTable: Select your data range, navigate to the Insert tab, and click PivotTable.
  3. 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. 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.
Seamless compatibility with Microsoft Excel (.xlsx) formatsBuilt-in PivotTable 'Difference From' feature for quick calculationsFree, lightweight, and fast processing for large datasets
microsoft office alternative - wps office

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.