Running total against weekly Capacity

Hi ARona,

According to your statement, I think your requirement is to accumulate the running total if the data is larger that 0.

I think Power BI doesn't support us to achieve looping. I suggest you to achieve your goal in Power Query Editor by M Query.

Add an Index column first.

Then add an custom column named B-C.

Then create a custom column based on them.

let 
_Limit = 0,
_CurrentIndex = [Index],
_CurrentRow = Table.SelectRows(#"Added Custom",each [Index] <= _CurrentIndex),
_CumulativeSum = List.Accumulate(
_CurrentRow[#"B-C"], 
    0, 
    (state, current) => 
        let
            _NewSum = state + current
        in
            if _NewSum < _Limit then _Limit else _NewSum
        )
in
_CumulativeSum

Result is as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdA9DsMgDAXguzAnEv4jcIvuKAfIVCn3H8qzqoriLEh82A9D7+l1X+87bankseR0bj1xZt0Ne646VimzE/zAqeTZ2ftXla8S/9T2jAy/gOrMiNC2hAxHiBIHx3Bm0Q0i0QuENfjhT6LgFRO2WN/gKquTf5l3/TuStQR++LGhHhtmJ30stlnPDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Year Week Text" = _t, #"Sum of Orders" = _t, WeeklyCapacity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year Week Text", type text}, {"Sum of Orders", Int64.Type}, {"WeeklyCapacity", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "B-C", each [Sum of Orders] - [WeeklyCapacity]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Backlog", each let 
_Limit = 0,
_CurrentIndex = [Index],
_CurrentRow = Table.SelectRows(#"Added Custom",each [Index] <= _CurrentIndex),
_CumulativeSum = List.Accumulate(
_CurrentRow[#"B-C"], 
    0, 
    (state, current) => 
        let
            _NewSum = state + current
        in
            if _NewSum < _Limit then _Limit else _NewSum
        )
in
_CumulativeSum),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"B-C"})
in
    #"Removed Columns"

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.