Using previous row in current row calculation

[Fraze]

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.

[ronrsnfld]

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

[smpa01]

@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]
)

[Fraze]

I tried this approach, but row 2 jumped 35k rather than 20k

[smpa01 · Accepted solution]

@Fraze  can you try this

Column = SUMX (
    FILTER (
        'Table',
        'Table'[Date] <= EARLIER ( 'Table'[Date] )
            && 'Table'[Customer] = EARLIER ( 'Table'[Customer] )
    ),
    'Table'[Net Change]
)

[smpa01]

@AlexisOlson  on the above, I have noticed that. if the TransactionID is text, the running total goes sideways, if my partition is on ID

However, it behaves properly I change that back to integer.

I thought DAx stores the text values as per the CHAR value

[AlexisOlson]

I'm not sure what you mean by "stores the text values as per the CHAR value" but the problem is that "1", "10", and "3" are all <= "3" sorted alphabetically.

[smpa01]

@AlexisOlson got it !!! many Thanks !!!!

[AlexisOlson]

You may be interested in these prior similar posts:
https://community.powerbi.com/t5/Desktop/Calculating-Daily-values-from-Cumulative-Total/m-p/2198969

https://community.powerbi.com/t5/Power-Query/Optimize-performance-at-Un-Cumulate-calculation/m-p/219...

[Fraze]

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

[AlexisOlson]

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