Running Total Formula Multiple Columns

[derekmac]

Hi I have 3 Columns in Power Query, the last column only has the Top Value in it

I need a formula that Starting from the null in the 2nd Row replaces that with 972 + 3 - 133 where 3 is the New Prospects Value in the 2nd Row and 133 is the CLosed ones

And then repeat this down with each row taking the total from the row above

Any advice appreciated!

[ronrsnfld]

Try this:

let

//change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
            {"End of week", type date}, {"New Prospects", Int64.Type}, {"Closed Prospects", Int64.Type}, {"Current Prospects.1", Int64.Type}}),

//Running Total List
    #"Current Prospects" = List.Generate(
        ()=>[rt=#"Changed Type"[Current Prospects.1]{0}, idx=0],
        each [idx] < Table.RowCount(#"Changed Type"),
        each [rt = [rt] + #"Changed Type"[New Prospects]{[idx]+1}- #"Changed Type"[Closed Prospects]{[idx]+1}, idx = [idx]+1],
        each [rt]),

//Replace Current Prospects column
    #"Running Total" = Table.FromColumns(
        Table.ToColumns(Table.RemoveColumns(#"Changed Type",{"Current Prospects.1"}))
         & {#"Current Prospects"},
         type table[End of Week=date, New Prospects = Int64.Type, Closed Prospects = Int64.Type, Current Prospects.1=Int64.Type])
in
    #"Running Total"

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...