Forum Discussion
Using previous row in current row calculation
Hello, trying to create an outstanding column using data from the previous row.
See example below for situation.
ID #1 has no previous data, so the outstanding balance is the Net change of $100,000. However ID #3 has an outstanding balance from the previous step, so the new outstanding balance is the difference. $80,000
ID #2 has previous data, but it is for a different customer. Therefore, the outstanding balance should be reset to 0 and the net change becomes the starting balance. $50,000. ID #5 has a previous outstanding balance with the same customer, so the net change is added to the previous row oustanding for an outstanding amount of $30,000
And so on for many transactions and numerous customers. Any thoughts on how to best do this in either the query editor or as a calcualted column would be much appreciated.
10 Replies
Good idea. But rather than extrapolating the cumultive totals, i need to create the cumulative totals by customer. Creating an index would not work because it would only match the previous value, rather than the cumulative previous values. happy to be wrong though if I am not seeing something
Whoops. I had the goal backward in my head (the links I gave wanted to deconstruct an existing cumulative column).
Here's how you can get the running total:
https://community.powerbi.com/t5/Desktop/How-to-do-a-running-Sum-by-group-in-Power-Query/m-p/290123
Fraze you can create a measure like this
Outstanding = VAR _filter = FILTER ( ALL ( 'Table' ), 'Table'[Transaction ID] <= MAX ( 'Table'[Transaction ID] ) && 'Table'[Customer] = MAX ( 'Table'[Customer] ) ) RETURN CALCULATE ( SUM ( 'Table'[NetChange] ), _filter )or a calculated column like this
Column = SUMX ( FILTER ( 'Table', 'Table'[Transaction ID] <= EARLIER ( 'Table'[Transaction ID] ) && 'Table'[Customer] = EARLIER ( 'Table'[Customer] ) ), 'Table'[NetChange] )It appears that you want to add a "running balance" column to each "group" of customers.
You can do that in M Code by Grouping, then using a custom aggregation to create the new subtable.
Please read the code comments for more detailed explanation:
let Source = Excel.CurrentWorkbook(){[Name="Table35"]}[Content], typeIt=Table.TransformColumnTypes(Source, { {"Transaction ID", Int64.Type},{"Customer",Text.Type},{"Date",Date.Type},{"Net Change",Currency.Type} }), //Group by Customer grp = Table.Group(typeIt,{"Customer"},{ //then add running balance column to each subtable {"runningBalance", (t)=> Table.FromColumns( Table.ToColumns(t) & {List.Generate( ()=>[rb=t[Net Change]{0}, idx = 0], each [idx]< Table.RowCount(t), each [rb = [rb]+ t[Net Change]{[idx]+1}, idx = [idx]+1], each [rb] )})}}), //Remove original customer table //Then expand the grouped table #"Removed Columns" = Table.RemoveColumns(grp,{"Customer"}), #"Expanded runningBalance" = Table.ExpandTableColumn(#"Removed Columns", "runningBalance", {"Column1", "Column2", "Column3", "Column4", "Column5"}, Table.ColumnNames(Source) & {"Outstanding"}), //reset the data types // could set up a more restricted environment than #shared if necessary types = List.Transform(Table.Schema(typeIt)[TypeName] & {"Currency.Type"}, each Expression.Evaluate(_,#shared) ), reType = Table.TransformColumnTypes(#"Expanded runningBalance",List.Zip({Table.ColumnNames(#"Expanded runningBalance"),types})) in reType
