Running total working backwards over time

Hi Anonymous 

You may create two columns to get the value.

Column =
IF (
    Table1[DateTime] = MAXX ( ALLEXCEPT ( Table1, Table1[ID] ), Table1[DateTime] ),
    Table1[Current Quantity] - Table1[Quant Add/Remove],
    Table1[Quant Add/Remove] * ( -1 )
)

Column 2 =
CALCULATE (
    SUM ( Table1[Column] ),
    FILTER (
        ALL ( Table1 ),
        Table1[ID] = EARLIER ( Table1[ID] )
            && Table1[DateTime] >= EARLIER ( Table1[DateTime] )
    )
)

Regards,

Cherie

M solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIFYmMQ1jc01TcyMLRUMDSwMjVVitVByOsaoSowtDIwQFFgiCGPZoAxugJDiAEgg4FMHSUjHE6AK9DFtAO/EXBH4DQCYkksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, CQ = _t, Value = _t, #"Date Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"CQ", Int64.Type}, {"Value", Int64.Type}, {"Date Time", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CQ"}, {{"AD", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn(
Table.Sort([AD],{{"Date Time", Order.Descending}}),"Rank",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AD"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID", "CQ", "Value", "Date Time", "Rank"}, {"ID", "CQ.1", "Value", "Date Time", "Rank"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Custom", {"CQ"}, {{"AD", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each let
   Source =[AD],
   Add_Row = Table.InsertRows(Source,0,{[CQ=null, ID=null, CQ.1=null, Value=List.First(Source[CQ]), Date Time=null, Rank=null]})
 in 
   Add_Row),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"AD"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Custom.1", each let
   Source=[Custom],
   Custom1 = List.Accumulate(Source[Value],{0},(state,current)=>if current=Source[Value]{0} then {current} else state&{List.Last(state)-(current)}),
    Custom2 = Table.FromColumns(Table.ToColumns(Source)&
{Custom1}),
 Remove= Table.RemoveRows(Custom2,0)
in
    Remove),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Custom", "CQ"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns2", "Custom.1", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Custom.1.Column1", "Custom.1.Column2", "Custom.1.Column3", "Custom.1.Column4", "Custom.1.Column5", "Custom.1.Column6", "Custom.1.Column7"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.1",{{"Custom.1.Column1", "ID"}, {"Custom.1.Column2", "CQ"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns",{"Custom.1.Column3"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns3",{{"Custom.1.Column4", "Q A/R"}, {"Custom.1.Column5", "Date Time"}, {"Custom.1.Column6", "Rank"}, {"Custom.1.Column7", "Desired Running Total"}})
in
    #"Renamed Columns1"