Calculate column with digited value

[alvaroiark]

Hi All,

I need to do the same calculation as in the image below, with custom column and m language, however the first value is manually inputed, that is, it is a fixed value, the sequence takes the prior value and adds and decreases the cells.

Best Regards,

Alvaro

[alvaroiark]

Hi @collinsg ,

It worked perfectly!

Thx so much!

Best Regards,

Alvaro

[collinsg · Accepted solution]

Good day Alvaro,

This problem is a variation on the theme of calculating a running total. Nick de Groot has a comprehensive article on running totals here. My solution uses a simple but slow approach to running total but if performance is a concern you could use one of the faster methods described by Nick.

My data looked like this – the balance column contained nulls except for the first row.

My steps were,

1. Replace the nulls in the Balance column with zero.
2. Add an index column.
3. Add a column called “i1+i2-o1-o2” which calculates Input1 + Input2 – Output1 – Output2.
4. Add a column called “Running Total” using the List.Sum and List.FirstN functions.
5. Remove unneeded column.
6. Rename “Running Total” as “Balance”.

There will be more elegant ways to do this without adding then removing columns but this step by step approach makes the route to the solution simple to follow.

This is my output

My M code is here.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Input1", Int64.Type}, {"Input2", Int64.Type}, {"Output1", Int64.Type}, {"Output2", Int64.Type}, {"Balance", Int64.Type}}),

    #"Replaced nulls in Balance" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Balance"}),
    #"Add Index" = Table.AddIndexColumn(#"Replaced nulls in Balance", "Index", 1, 1, Int64.Type),
    #"Added i1+i2-o1-o2" = Table.AddColumn(#"Add Index", "i1+i2-o1-o2", each [Balance] + [Input1] + [Input2] - [Output1] - [Output2], Int64.Type),
    #"Add running total" = Table.AddColumn(
        #"Added i1+i2-o1-o2", 
        "Running Total", 
        each List.Sum( List.FirstN( #"Added i1+i2-o1-o2"[#"i1+i2-o1-o2"], [Index] ) ),
        Int64.Type ),
    #"Remove other columns" = Table.SelectColumns(#"Add running total",{"Date", "Input1", "Input2", "Output1", "Output2", "Running Total"}),
    #"Renamed Columns" = Table.RenameColumns(#"Remove other columns",{{"Running Total", "Balance"}})
in
    #"Renamed Columns"

Hope this helps