Replicate excel formula - recursive substraction

[Anonymous]

Hi there. I am trying to replicate excel formula in power BI DAX. I have date column(A) with date, progress column(B) with integer, left column(C) with integer. This meant to be project progress monitoring tool. So the team is starting with 100k instances to process and on each day they do certain progress, so in the cell C2 = 100000-B2,but starting from C3 = C2 - B3, C4 = C3 - B4 and so on...

Help will be appreciated.

Thanks,

Michal

[ImkeF]

Hi @Anonymous ,

if you're serious about performance here, I'd recommend this approach:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMDJR0lQwMQUIrVgYgbIcThYsYwMSOEmAlMzBghZopFrxmy3lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, progress = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"progress", Int64.Type}}),
    ListToAccumulate =  List.Buffer(#"Changed Type"[progress]),
    AccumulateColumn = List.Generate( ()=>
        [Result = List.First(ListToAccumulate), Counter = 1 ],
        each [Counter] <= List.Count(ListToAccumulate),
        each [
                Result = [Result] - ListToAccumulate{[Counter]},
                Counter = [Counter] + 1
        ],
        each [Result]
        
    ),
    Custom2 = Table.FromColumns(Table.ToColumns( #"Changed Type") & {AccumulateColumn}, Table.ColumnNames(#"Changed Type") & {"Left"})
in
    Custom2

Copy the code, paste it into the advanced editor of a new query and follow the steps.