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?
I think I figured it out (again).
For each processing time slot I re-sort the remaining orders by fulfillment time first, then by placed time. That way changes in priority can be handled.
let
//process new timeslot
FillShift = (tbl, slot) => let
// save away already processed entries
prc = Table.SelectRows(Table.Buffer(tbl),each [Processed]<> null),
// items that need processing
src=Table.SelectRows(tbl,each [Processed] = null),
// sort by fulfilled, then placed
srt = Table.Sort(src,{{"Fulfillment Time", Order.Ascending}, {"Placed Time", Order.Ascending}}),
// add index
idx = Table.AddIndexColumn(srt, "Index", 0, 1, Int64.Type),
// tag lines to process
rep = Table.ReplaceValue(idx,null,each if [Placed Time]<=#time(slot,0,0) and [Index]<140 then #time(slot,0,0) else null,Replacer.ReplaceValue,{"Processed"}),
// drop index
res = Table.RemoveColumns(rep,{"Index"})
in
//concatenate result with saved portion
prc & res,
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}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "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 Custom1" = Table.AddColumn(#"Removed Other Columns", "Processed", each null),
Filled = List.Accumulate({4..19},#"Added Custom1",(state,current)=>FillShift(state,current)),
#"Changed Type1" = Table.TransformColumnTypes(#"Filled",{{"Processed", type time}})
in
#"Changed Type1"This is almost exactly what I need!!!! Maybe I'm not understanding the full implications of the Table.Sort step but, for example, why does it not bump down some of the 11 am orders that were placed by 4 am to be processed at 5 am with the rest? Ideally I want to use up my capacity early in the day to avoid the late orders in the afternoon.
In other words, how do I make it prioritize being on-time until inevitable?
That is a problem with your sort logic. Remember, for each remaining slot the orders are sorted first by fulfillment time and then by placed time.
This will create gaps in the 140 orders bucket that cannot be compensated unless you change the logic.
The proper logic should be: fill each production slot with orders based on fulfillment time first, placed time second until either:
- the 140 limit is hit
- the placed time limit is hit
- there are no more orders
This likely requires a total refactoring of the code, and the use of recursive functions - something you rather want to avoid, as this increases the complexity from "82 orders placed" to "touch every single order"
@lbendlin Spot on what i needed! Thanks so much for the help!
Can you walk me thorugh how the FillShift function works? I've been playing around invoking the function with test parameters and I can't seem to get the correct format. I just want to understand how it knows what the slot is at any point
Thank you for providing the sample data. Please walk me through the logic again for the 4am row based on the sample data.
Will any order placed before 7am be put on hold until then?
Does 4am actually mean "overnight, after 8pm of the prior day" ?
The logic is the following:
For every 'Order Placed' timeslot, I can only fulfill 140 orders (Sorry, my original message and formulas said 100, but the screenshot calculations are right). From your screenshot, that would mean I can fulfill all the orders due at 7 am (29), all the orders due at 8 am (81), and only 30 out of the 70 that are due at 9 am (and so on so forth till the end of the row, as the screenshot in my original post shows).
Once 5 am hits, I would takle first the 40 orders for 9 am that i didnt get to in the previous timeslot plus the other 5 orders underneath. and so on so forth till the end of the row.
In summary, for every "placed time" (matrix rows), I can only fulfill 140 orders, but since I have more orders than I have capacity, I need to make up for it on the next timeslot together with the orders at that timeslot. My goal with this matrix is to show where the whole system breaks and at how many orders. Hope the context helps! Let me know if you have other questions! Thanks for the help!
by the way here is my version of the running total for the Placed date:
RT =
var f = max('Table'[Fulfillment Time]) return CALCULATE(sum('Table'[Orders]),'Table'[Fulfillment Time]<=f)
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
