Others

Multiply Power BI Values by the Previous Row with Power Query

Olivia MillerOlivia Miller Nov 20, 2026 868 views

Question details

Calculate each Power BI row from the previous row without relying on consecutive calendar dates.

Multiply Power BI Values by the Previous Row with Power Query
Product
Power BI Power Query
Device & OS
not provided
Scenario
A table has values by date, but weekend dates are missing. The user needs a cumulative calculation based on the preceding available row.
Observed behavior
A calculation that subtracts one calendar day returns blanks when the preceding date is not present.
Before you start

Identify the date column and the numeric B column, and confirm that B values are stored as decimals, such as 0.05 for 5%. Sort rows into the intended calculation order; if dates repeat, define a secondary sort column.

Solution 1Recommended

Calculate the cumulative product with a Power Query index

Use row position instead of calendar-day subtraction so missing weekends do not interrupt the calculation.

This example starts the first row at 1,000. Each later result equals the previous result multiplied by 1 plus the previous row's B value.

The example assumes the source query is named Data and its columns are named Date and B. Replace these names in the code if yours are different, and ensure B contains no nulls or errors.

1
Open Power Query

In Power BI Desktop, select Home > Transform data. Confirm that your source query contains the Date and B columns.

2
Create a calculation query

In Power Query Editor, select Home > New Source > Blank Query, then open Home > Advanced Editor.

3
Enter the indexed calculation

Replace the blank query code with: let Source = Data, Sorted = Table.Sort(Source, {{"Date", Order.Ascending}}), Factor = Table.AddColumn(Sorted, "Factor", each 1 + [B], type number), Indexed = Table.AddIndexColumn(Factor, "Index", 1, 1, Int64.Type), Factors = List.Buffer(Indexed[Factor]), Result = Table.AddColumn(Indexed, "Result", each 1000 * List.Product(List.FirstN(Factors, [Index] - 1)), type number) in Result. Select Done.

4
Check and load the result

Check the Result column: the first row should be 1,000, and the second should be 1,000 multiplied by 1 plus the first row's B value. Select Home > Close & Apply to load the query.

Calculate the cumulative product with a Power Query index
Choose the intended row order: If Date values are not unique, add a secondary sort column to Table.Sort so the previous row is unambiguous. To include the current row's factor instead, change [Index] - 1 to [Index].
Free Microsoft Office alternative

Calculate Previous-Row Products in WPS Spreadsheets

For a worksheet-based version of this calculation, use WPS Spreadsheets as a free, lightweight Office alternative. Its familiar interface and Excel format support make it easy to move spreadsheet data, although it does not repair a Power BI query.

  1. 1. Open your spreadsheet: Open WPS Spreadsheets and use File > Open to load an Excel workbook containing dates and B values.
  2. 2. Sort the rows: Select the data and use Data > Sort to place the dates in the intended calculation order. Add a secondary sort key if dates repeat.
  3. 3. Enter the formulas: If B values begin in B2, enter 1000 in C2 and =C2*(1+B2) in C3. Drag the fill handle from C3 down the Result column.
Calculate running products using the previous available spreadsheet rowOpen and save Microsoft Excel XLSX files for easier migrationWork in a familiar, easy-to-use spreadsheet interfaceUse a free, lightweight alternative for everyday Office tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does subtracting one day return a blank after a weekend?

The preceding calendar date may not exist in the table. Sorting the rows and using a Power Query index lets the calculation refer to the preceding available row instead.

How do I change the starting value from 1,000?

In Power Query Editor, open Home > Advanced Editor and replace 1000 in the Result calculation with your starting value.

Does the calculation use the current row's B value?

As written, it uses factors from preceding rows only. In Advanced Editor, change [Index] - 1 to [Index] if the current row's factor should also be included.

What if two rows have the same date?

Add a secondary sort column in the Table.Sort step, such as a transaction sequence, so Power Query has a consistent order for those rows.