Accumulated Reduction of a Budget based on another Column

[Tim_Ba]

Hi there, 

I am currently setting up a forecast query for project controlling.

As you can see in the attached snip, I have "Current Budget" as initial value with Index 0.

In a new column the "Remaining Budget" should be calculated:

Remaining Budget (index) = Remaining Budget (index-1) - Expected Bill Amount (aggregated)
The first Remaining Budget for index=0 should just be the "Current Budget".

Thanks a lot in advance!

Best, Tim

[dufoq3 · Accepted solution]

Hi @Tim_Ba, there are many ways like @m_dekorte mentioned, but this one is the fastest

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddC9DQAhCIDRVQy1MQh4P7MY919DuOKkAGP1PYMJcwJRK9wKIQlU0NvRDqyqxt4U7MUHksHI4PJw8h3nx+dx+ht3xqT3cLxWCT711bbwT8lAQlgb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Expected Bill Amount (aggregated)" = _t, #"Current Budget" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Expected Bill Amount (aggregated)", Currency.Type}, {"Current Budget", Currency.Type}}, "sk-SK"),
    GenerateBudget = [ a = List.Buffer(ChangedType[#"Expected Bill Amount (aggregated)"]),
    lg = List.Generate(
            ()=> [ x = 0, y = ChangedType{0}[Current Budget] ],
            each [x] < List.Count(a),
            each [ x = [x]+1, y = [y]-a{x} ],
            each [y]
         )
    ][lg],
    Merged = Table.FromColumns(Table.ToColumns(ChangedType) & {GenerateBudget}, Value.Type(ChangedType & #table(type table[Remaining Budget=Currency.Type], {{}})))
in
    Merged

[Tim_Ba]

Hi @dufoq3, many thanks for this straightforward solution. It was quite easy to implement and understand!

Also thanks to @m_dekortefor the effort. As I am a beginner in coding for PowerQuery, I think the solution was a little too advanced for me at this point as I failed to implemented it.

Best, Tim

[dufoq3]

I've just edited last step. Now you there is a Remaining Budget with correct type Currency.Type directly. No additional step Changed Type needed.

[dufoq3]

You're welcome.

[m_dekorte]

Hi @Tim_Ba,

There are several ways to achieve this, a straight forward method is using List.Range

let
    curBudget = Source[Budget]?{0}?,
    expAmount = List.Buffer( List.ReplaceValue( Source[ExpectedAmt], null, 0, Replacer.ReplaceValue )),
    Source = Table.FromColumns(
        {
            {null} & {4,6,7,3,9,3,5,7, 8},
            {100} & List.Repeat({null}, 9),
            {0..9}
        }, type table
        [ExpectedAmt = number, Budget=number, Index=number]
    ),
    InsertRemaining = Table.AddColumn(Source, "Remaining Budget", each curBudget - List.Sum( List.Range(expAmount, 0, [Index]+1 )))
in
    InsertRemaining

From the Source table, I've extracted the starting value and stored that in the curBudget variable. And the Expected Amount column is loaded into memory, stored in the expAmount variable. As M applies null propagation to avoid errors I've replaced that value with a 0. Here's the result.

I hope this is helpful.