Forum Discussion

ARona's avatar
ARona
Regular Visitor
1 year ago
Solved

Running total against weekly Capacity

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 inc...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

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's avatar
ARona
Regular Visitor
1 year ago

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's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      ARona
      Regular Visitor
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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.