Running total against weekly Capacity

[ARona]

Hi everyone, 

I hope someone can help. 

I have a table that I can filter by product, displaying weekly orders and respective capacity on a graph. I want to add a Backlog Impact column and include it in the graph as well. In Excel, the table looks as below: 

Year Week Text	Sum of Orders	WeeklyCapacity	Backlog
Prior	60	0	60
2024-50	284	360	0
2024-51	270	300	0
2024-52	0	300	0
2024-53	0	120	0
2025-01	50	180	0
2025-02	490	300	190
2025-03	412	300	302
2025-04	552	300	554
2025-05	332	300	586
2025-06	324	300	610
2025-07	271	300	581
2025-08	194	300	475
2025-09	143	300	318
2025-10	208	300	226
2025-11	46	300	0
2025-12	0	300	0
2025-13	32	300	0
2025-14	0	300	0
2025-15	0	300	0

For the "Prior" row, the value is taken directly from B2 (e.g., 60 in this example, but it varies by product). For each subsequent row, the formula is:

=MAX(0, D2 + B3 - C3)

I've tried creating several measures in Power BI to replicate this, but none have worked so far. Here's the table in Power BI:

My last attempt at a measure (Cumulative Overdue) was:

Cumulative Overdue =

VAR CurrentWeek = MAX(WorkingDays[Year Week Text])

VAR CumulativeValue =

    SUMX(

        FILTER(

            ALL(WorkingDays),

            WorkingDays[Year Week Text] <= CurrentWeek

        ),

        'WorkingDays'[OverdueUpd]

    )

RETURN

    IF(CumulativeValue < 0, 0, CumulativeValue)

 

I’d really appreciate any guidance on how to make this work... Thanks in advance!

[bhanu_gautam]

@ARona , First You can use DAX to create a calculated column that computes the backlog for each week. 

DAX
Backlog =
VAR PreviousBacklog =
IF(
ISBLANK(WorkingDays[Year Week Text]),
0,
CALCULATE(
MAX(WorkingDays[Backlog]),
FILTER(
WorkingDays,
WorkingDays[Year Week Text] = EARLIER(WorkingDays[Year Week Text]) - 1
)
)
)
RETURN
MAX(0, PreviousBacklog + WorkingDays[Sum of Orders] - WorkingDays[WeeklyCapacity])

If you need a measure to calculate the cumulative overdue,

DAX
Cumulative Overdue =
VAR CurrentWeek = MAX(WorkingDays[Year Week Text])
VAR CumulativeValue =
SUMX(
FILTER(
ALL(WorkingDays),
WorkingDays[Year Week Text] <= CurrentWeek
),
WorkingDays[Backlog]
)
RETURN
IF(CumulativeValue < 0, 0, CumulativeValue)

[ARona]

Thank you very much - this is the error I now get: 

I also modified line 15 to use SUM(OpenOrderBook[Total Quantity]), as the total order quantity is pulled from a different table (OpenOrderBook). Not sure if this will work either..

[bhanu_gautam]

@ARona , Try this DAX then using EARLIER

Backlog =
VAR PreviousBacklog =
CALCULATE(
MAX(WorkingDays[Backlog]),
FILTER(
WorkingDays,
WorkingDays[Year Week Text] = EARLIER(WorkingDays[Year Week Text]) - 1
)
)
RETURN
IF(
ISBLANK(PreviousBacklog),
0,
MAX(0, PreviousBacklog + WorkingDays[Sum of Orders] - WorkingDays[WeeklyCapacity])
)

[ARona]

A few errors came up trying the earlier, as per the below: 

I don't think I can use Earlier, as it requires name of the column, whereas Year Week Text isn't saved as a column.. Also, I don't have a column with 'Backlog', that's the result I want to achieve, as per the top screenshot from Excel. Thank you! 

[Anonymous · Accepted solution]

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.