Multiply Power BI Values by the Previous Row with Power Query
Question details
Calculate each Power BI row from the previous row without relying on consecutive calendar dates.

- 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.
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.
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.
In Power BI Desktop, select Home > Transform data. Confirm that your source query contains the Date and B columns.
In Power Query Editor, select Home > New Source > Blank Query, then open Home > Advanced Editor.
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.
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 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. Open your spreadsheet: Open WPS Spreadsheets and use File > Open to load an Excel workbook containing dates and B values.
- 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. 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.

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.




