Substraction that tallies from Running Sum

[Anonymous]

Consider the following dataset where placed time goes from 4am to 8pm and fulfillment time goes from 7am to 8pm as well.

I calculated the Running Sum, resulting in this matrix

For every timeslot in the y axis, I can only fulfill 100 orders. I want to calculate a matrix in the same format as above that tells me the remaining orders to fulfill every hour after that 100 runs out. This is the expected result for 4:00 am:

 Which I calculated using the following formula:

Column = max(Table'[Running Total]-100,0)

The issue is that I want every row to pick back up with the first non-zero value from the previous row. In this case, that value would be 40. In other words, since I can only fulfill 100 orders in an hour, at 5 am I am tackling the 40 orders that I couldn't get to at 4 am + the ones that were placed at 5 am. Does anyone have any suggestions for my requierement?

[lbendlin]

When does production actually start? and once it starts, will you always handle 140 orders per hour, even orders that are due later?

[Anonymous]

Yes, once it starts we can only take care of 140 per hour. Thats why production starts that early; bulk of orders are due between 4 and 6 pm

[lbendlin]

So you could do some basic math - 140 * 14 = 1960. You can handle 1960 orders per day between 4am and 6pm.  Even your full day total is far below that. The spike in due order volume between 3pm and 5pm can be absorbed in the slots before.

[Anonymous]

Yup. Just looking for an hour by hour breakdown

[Anonymous]

Production starts at 4am. You can sort of picture it as a bakery operation if that helps make sense of the lead times.

[lbendlin]

is there a penalty for completing an order early? is there a penalty for completing an order late?

[Anonymous]

No penalty for early. We shouldn't complete an order late, but thats the info the matrix will tell us; at what point does the system break

[lbendlin]

"at what point does the system break" That's simple - take the cumulative of all due orders for a given hour slot, then compare that to 140 times the number of elapsed hours.

[lbendlin]

[Anonymous]

This is good for the overall picture! 

I'm looking for the hour by hour breakdown for the specifics. This doesn't tell me at what time I'll be done handling 4 pm orders, or how many production timeslots it took me to get there. That's why it matters 

[lbendlin]

Understand. DAX is definitely unable to do that.  Still tinkering with Power Query but so far haven't gotten the logic pudding nailed to the wall.

[Anonymous]

Appreciate this!!

[lbendlin]

I got it.

Instead of sorting by placed time first, I am sorting by fulfillment time first. Then I expand the number of orders for each slot for easier counting. After that it is a simple integer division by 140.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZdLkiMhDAXv4nUvLMRPc5WOuf81ZlGIqafIFeEMTKG0oJ5/fz/9z/f7+fmsZ2jx+ftz4T6DvWGcL3zf0L7POGWq2TOOKbQ9Y29C/dAutNO+bDyj67rzPG0Jzcp0Zzv3IvTU5m/YTmlPvUN0CdvAzoLjzYwWTFUmsBF0gp3WHASPpSaQ6jEqKBUJVEUTFE1RZG8WMC8VycRU5AIbQac1O80cBLORBC6auelBVJEqWqBoQRctUeRvpoqWKmpDaLaRznWkx9IUeBtK6LUtNOsQeKrqAuPdiYs8bfC0wdOGVtrqqQs0mtlE3i6altB7Pwm950HobRah4mmrJ90BldXk5w/wFOApwFPQrRTqaQqUIxeqyZfQvEO30NQ0hOZJ0XXzqAhMCQLj/UMHacoaxVPCTTAIqqpLDWl7G7zU3wovzRuq4ME4fS3FeZC64jw2SoP2oWcw61oEN8EgaLhokWYqrVAXlabSutLizNRZxXm3N8X3ClEctI8irZG0RtIaSWsoraG0htKaSis0s5zSm0UU3zeg4lNbL0vfe1OxSmsq7Xh3kuYkzUmaozRHaY7SHKW5Siv0JjDFU126SmsF54WmIsrxdJTWSVonaZ2kdZTWUVpHaR2ldZRWAnyhN/0pxtJun23F970hOJ0dk5TijWK8DXKGQd4GOhvobKAzDPOGad5KnC90qcmS6LvS7LImuLQZpXqbpIxyvU1UNlHZRGWY7W2isonKJior+b5QCWRWEn4XerPGOdsU8m/qIxgEi7GFxhYaW2hsobGFxhYaW2isRP1CJevfW1BPJaX9m1sIBsEiDBP//7//SB1pRzqQTqRYmWFphrXdFjuYcn/CTTAIFmOBxgKNBRoLNBZoLNBYoLFAY4HGyh+Ah+anRXATDIKGi6qwSxtSR9qRDqQTKVZmWJphbS9hf/8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Placed Time" = _t, #"Fulfillment Time" = _t, Orders = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Orders] <> "0")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Placed Time", type time}, {"Fulfillment Time", type time}, {"Orders", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Fulfillment Time", Order.Ascending},{"Placed Time", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Order Number", each {1..[Orders]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Order Number"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Custom", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Processed", each (4 + Number.IntegerDivide([Index],140) )/24),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Processed", type time}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Placed Time", "Fulfillment Time", "Order Number", "Processed"})
in
    #"Removed Other Columns"

If you like you can add a flag that indicates when the "actually processed" time is after the "fulfillment time" (ie when the process breaks).  Your sample data can be processed without that flag being triggered.

[Anonymous]

Thank you! This is still quite not there but we're close.

I can't fulfill an order that hasn't been placed yet. I'm finishing production at 2 pm according to your graph, but about half of orders due at 7 pm haven't been placed then. This goes back to one of your questions; and i correct that it does matter when the order was placed.

[lbendlin]

That would make it an iterative process.  I'll see if there is a way to run this in Power Query. For now I have simply moved the actual fulfillment time to be no earlier than the placed time, and you can see when it starts breaking.

[lbendlin]

@Anonymous I got it ! (again...)  This time the algorithm is based on "First Come First Serve" Meaning orders are processed in the order they are received, including downtime when there are fewer orders than capacity.

I also included a "Late"  flag (when the processed time is later than the expected fulfillment time ) but your sample data doesn't raise that flag.

The query is a monster, but it has to be as neither Power BI nor Power Query have a concept of "variables"  (they may call it variables but all of theirs are immutable).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZdLkiMhDAXv4nUvLMRPc5WOuf81ZlGIqafIFeEMTKG0oJ5/fz/9z/f7+fmsZ2jx+ftz4T6DvWGcL3zf0L7POGWq2TOOKbQ9Y29C/dAutNO+bDyj67rzPG0Jzcp0Zzv3IvTU5m/YTmlPvUN0CdvAzoLjzYwWTFUmsBF0gp3WHASPpSaQ6jEqKBUJVEUTFE1RZG8WMC8VycRU5AIbQac1O80cBLORBC6auelBVJEqWqBoQRctUeRvpoqWKmpDaLaRznWkx9IUeBtK6LUtNOsQeKrqAuPdiYs8bfC0wdOGVtrqqQs0mtlE3i6altB7Pwm950HobRah4mmrJ90BldXk5w/wFOApwFPQrRTqaQqUIxeqyZfQvEO30NQ0hOZJ0XXzqAhMCQLj/UMHacoaxVPCTTAIqqpLDWl7G7zU3wovzRuq4ME4fS3FeZC64jw2SoP2oWcw61oEN8EgaLhokWYqrVAXlabSutLizNRZxXm3N8X3ClEctI8irZG0RtIaSWsoraG0htKaSis0s5zSm0UU3zeg4lNbL0vfe1OxSmsq7Xh3kuYkzUmaozRHaY7SHKW5Siv0JjDFU126SmsF54WmIsrxdJTWSVonaZ2kdZTWUVpHaR2ldZRWAnyhN/0pxtJun23F970hOJ0dk5TijWK8DXKGQd4GOhvobKAzDPOGad5KnC90qcmS6LvS7LImuLQZpXqbpIxyvU1UNlHZRGWY7W2isonKJior+b5QCWRWEn4XerPGOdsU8m/qIxgEi7GFxhYaW2hsobGFxhYaW2isRP1CJevfW1BPJaX9m1sIBsEiDBP//7//SB1pRzqQTqRYmWFphrXdFjuYcn/CTTAIFmOBxgKNBRoLNBZoLNBYoLFAY4HGyh+Ah+anRXATDIKGi6qwSxtSR9qRDqQTKVZmWJphbS9hf/8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Placed Time" = _t, #"Fulfillment Time" = _t, Orders = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Orders] <> "0")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Placed Time", type time}, {"Fulfillment Time", type time}, {"Orders", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Placed Time", Order.Ascending},{"Fulfillment Time", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Order Number", each {1..[Orders]}),
    #"Expanded Order Number" = Table.ExpandListColumn(#"Added Custom", "Order Number"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Order Number",{"Placed Time", "Fulfillment Time", "Order Number"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Processed", each null),
    FillShift = (tbl, slot) => let
        rep = Table.ReplaceValue(tbl,null,each if [Processed] = null and [Placed Time]<=#time(slot,0,0) and [Index]<140 then #time(slot,0,0) else null,Replacer.ReplaceValue,{"Processed"}),
        NextIndex=Table.SelectRows(rep,each [Processed] = null)[Index]{0}
        in  
        Table.ReplaceValue(rep,each [Index],each if [Processed] = null then [Index]-NextIndex else null,Replacer.ReplaceValue,{"Index"}),
    Filled = List.Accumulate({4..19},#"Added Custom1",(state,current)=>FillShift(state,current)),    
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled",{{"Processed", type time}})

in
    #"Changed Type1"

[Anonymous]

@lbendlin You're awesome!!!!! I modified the Late flag logic to this.

Late = if('140 Cap'[Processed]>='140 Cap'[Fulfillment Time],1,0)

Can we modify the "first come first served" logic to a "First come first served" unless there's orders due sooner that we haven't processed yet. Meaning, at 10am, I don't want to prioritize an order due at 4pm if I have an 11 am order that we haven't processed yet.

[lbendlin]

No, that is definitely outside of what Power Query can do. As I mentioned, neither Power BI nor Power Query has any memory or concept of variables. You would need variables for that unless you come up with a more clever way of (re)sorting the incoming orders.

This is what the queue looks like if you simply switch the sort order (fulfilment time , then placed time)

[Anonymous]

How about scratching the first come first served logic and instead just prioritize orders with the smallest fulfillment time as they come in? 

[lbendlin]

see response 32. If you were to do the allocations manually, how would you do them and why?