Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to Solution.
I had alredy added the comments in the function
FillShift = (tbl, slot) => let
// buffer the table
buf = Table.Buffer(tbl),
// save away already processed entries
prc = Table.SelectRows(buf,each [Processed]<> null),
// items that need processing. consider only placed orders.
src=Table.SelectRows(buf,each [Processed] = null and [Placed Time] <= #time(slot,0,0)),
// defer (orders were placed later)
def = Table.SelectRows(buf,each [Processed] = null and [Placed Time] > #time(slot,0,0)),
// 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 portions
prc & res & def
The function expects a table with the four required columns Placed Time, Fulfillment Time, Order identifier and Processed. (The order identifier is generated when exploding the number of received orders into a list)
The function also expects a time slot for which the computation needs to happen . That is the hour number.
First step is to buffer the entire table to improve query performance
Next step is to save away the portion of the orders (prc) that have already been allocated (that have a Processed value)
Then we identify all orders that can be processed - meaning the Placed Time is before or equal to the time slot
Then we save away (defer) all orders that cannot yet be processed as the Placed Time is after the time slot
Next we sort the eligible orders by fulfillment time and placed time
Then we tag the first 140 orders (or less) with the processed time stamp of the current slot
And finally we combine the three parts back together, ready for the next timeslot.
The function is then called for each available timeslot (from 4 to 19) sequentially.
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"
You are absolutely correct. You've helped me figure out the logic behind what I was already doing on paper by instinct 🙂
I don't mind the query running slower. What modifications would i need to make to the dataset for the recursive function? What would that look like?
I managed to do it without recursions. See the comments in the code.
let
//process new timeslot
FillShift = (tbl, slot) => let
// buffer the table
buf = Table.Buffer(tbl),
// save away already processed entries
prc = Table.SelectRows(buf,each [Processed]<> null),
// items that need processing. consider only placed orders.
src=Table.SelectRows(buf,each [Processed] = null and [Placed Time] <= #time(slot,0,0)),
// defer (orders were placed later)
def = Table.SelectRows(buf,each [Processed] = null and [Placed Time] > #time(slot,0,0)),
// 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 portions
prc & res & def,
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"
@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
I had alredy added the comments in the function
FillShift = (tbl, slot) => let
// buffer the table
buf = Table.Buffer(tbl),
// save away already processed entries
prc = Table.SelectRows(buf,each [Processed]<> null),
// items that need processing. consider only placed orders.
src=Table.SelectRows(buf,each [Processed] = null and [Placed Time] <= #time(slot,0,0)),
// defer (orders were placed later)
def = Table.SelectRows(buf,each [Processed] = null and [Placed Time] > #time(slot,0,0)),
// 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 portions
prc & res & def
The function expects a table with the four required columns Placed Time, Fulfillment Time, Order identifier and Processed. (The order identifier is generated when exploding the number of received orders into a list)
The function also expects a time slot for which the computation needs to happen . That is the hour number.
First step is to buffer the entire table to improve query performance
Next step is to save away the portion of the orders (prc) that have already been allocated (that have a Processed value)
Then we identify all orders that can be processed - meaning the Placed Time is before or equal to the time slot
Then we save away (defer) all orders that cannot yet be processed as the Placed Time is after the time slot
Next we sort the eligible orders by fulfillment time and placed time
Then we tag the first 140 orders (or less) with the processed time stamp of the current slot
And finally we combine the three parts back together, ready for the next timeslot.
The function is then called for each available timeslot (from 4 to 19) sequentially.
That's likely not something you can do in DAX. It might be possible in Power Query.
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
This is my dataset. I calculated the Running Total wit the following formula:
calculate(sum(table[Orders]), all(table),table[Placed Time] <= earlier(table[Placed Time ]), table[Fulfillment Time] <= earlier(table[Fulfillment Time]))
Placed Time | Fulfillment Time | Orders |
4:00 | 7:00 | 29 |
4:00 | 8:00 | 81 |
4:00 | 9:00 | 70 |
4:00 | 10:00 | 61 |
4:00 | 11:00 | 56 |
4:00 | 12:00 | 42 |
4:00 | 13:00 | 44 |
4:00 | 14:00 | 29 |
4:00 | 15:00 | 36 |
4:00 | 16:00 | 57 |
4:00 | 17:00 | 21 |
4:00 | 18:00 | 12 |
4:00 | 19:00 | 3 |
4:00 | 20:00 | 0 |
5:00 | 7:00 | 0 |
5:00 | 8:00 | 0 |
5:00 | 9:00 | 5 |
5:00 | 10:00 | 0 |
5:00 | 11:00 | 1 |
5:00 | 12:00 | 1 |
5:00 | 13:00 | 1 |
5:00 | 14:00 | 0 |
5:00 | 15:00 | 0 |
5:00 | 16:00 | 2 |
5:00 | 17:00 | 0 |
5:00 | 18:00 | 0 |
5:00 | 19:00 | 0 |
5:00 | 20:00 | 0 |
6:00 | 7:00 | 0 |
6:00 | 8:00 | 1 |
6:00 | 9:00 | 0 |
6:00 | 10:00 | 1 |
6:00 | 11:00 | 3 |
6:00 | 12:00 | 3 |
6:00 | 13:00 | 0 |
6:00 | 14:00 | 3 |
6:00 | 15:00 | 3 |
6:00 | 16:00 | 5 |
6:00 | 17:00 | 3 |
6:00 | 18:00 | 0 |
6:00 | 19:00 | 0 |
6:00 | 20:00 | 0 |
7:00 | 7:00 | 0 |
7:00 | 8:00 | 0 |
7:00 | 9:00 | 3 |
7:00 | 10:00 | 1 |
7:00 | 11:00 | 25 |
7:00 | 12:00 | 11 |
7:00 | 13:00 | 11 |
7:00 | 14:00 | 6 |
7:00 | 15:00 | 15 |
7:00 | 16:00 | 13 |
7:00 | 17:00 | 8 |
7:00 | 18:00 | 4 |
7:00 | 19:00 | 2 |
7:00 | 20:00 | 0 |
8:00 | 7:00 | 0 |
8:00 | 8:00 | 0 |
8:00 | 9:00 | 0 |
8:00 | 10:00 | 4 |
8:00 | 11:00 | 0 |
8:00 | 12:00 | 25 |
8:00 | 13:00 | 17 |
8:00 | 14:00 | 14 |
8:00 | 15:00 | 20 |
8:00 | 16:00 | 18 |
8:00 | 17:00 | 8 |
8:00 | 18:00 | 5 |
8:00 | 19:00 | 0 |
8:00 | 20:00 | 1 |
9:00 | 7:00 | 0 |
9:00 | 8:00 | 0 |
9:00 | 9:00 | 0 |
9:00 | 10:00 | 0 |
9:00 | 11:00 | 6 |
9:00 | 12:00 | 3 |
9:00 | 13:00 | 37 |
9:00 | 14:00 | 28 |
9:00 | 15:00 | 25 |
9:00 | 16:00 | 23 |
9:00 | 17:00 | 7 |
9:00 | 18:00 | 2 |
9:00 | 19:00 | 4 |
9:00 | 20:00 | 1 |
10:00 | 7:00 | 0 |
10:00 | 8:00 | 0 |
10:00 | 9:00 | 0 |
10:00 | 10:00 | 0 |
10:00 | 11:00 | 0 |
10:00 | 12:00 | 6 |
10:00 | 13:00 | 3 |
10:00 | 14:00 | 33 |
10:00 | 15:00 | 33 |
10:00 | 16:00 | 27 |
10:00 | 17:00 | 24 |
10:00 | 18:00 | 8 |
10:00 | 19:00 | 3 |
10:00 | 20:00 | 0 |
11:00 | 7:00 | 0 |
11:00 | 8:00 | 0 |
11:00 | 9:00 | 0 |
11:00 | 10:00 | 0 |
11:00 | 11:00 | 0 |
11:00 | 12:00 | 0 |
11:00 | 13:00 | 6 |
11:00 | 14:00 | 4 |
11:00 | 15:00 | 33 |
11:00 | 16:00 | 33 |
11:00 | 17:00 | 32 |
11:00 | 18:00 | 10 |
11:00 | 19:00 | 6 |
11:00 | 20:00 | 0 |
12:00 | 7:00 | 0 |
12:00 | 8:00 | 0 |
12:00 | 9:00 | 0 |
12:00 | 10:00 | 0 |
12:00 | 11:00 | 0 |
12:00 | 12:00 | 0 |
12:00 | 13:00 | 0 |
12:00 | 14:00 | 9 |
12:00 | 15:00 | 11 |
12:00 | 16:00 | 17 |
12:00 | 17:00 | 40 |
12:00 | 18:00 | 13 |
12:00 | 19:00 | 6 |
12:00 | 20:00 | 2 |
13:00 | 7:00 | 0 |
13:00 | 8:00 | 0 |
13:00 | 9:00 | 0 |
13:00 | 10:00 | 0 |
13:00 | 11:00 | 0 |
13:00 | 12:00 | 0 |
13:00 | 13:00 | 0 |
13:00 | 14:00 | 0 |
13:00 | 15:00 | 14 |
13:00 | 16:00 | 11 |
13:00 | 17:00 | 21 |
13:00 | 18:00 | 22 |
13:00 | 19:00 | 3 |
13:00 | 20:00 | 2 |
14:00 | 7:00 | 0 |
14:00 | 8:00 | 0 |
14:00 | 9:00 | 0 |
14:00 | 10:00 | 0 |
14:00 | 11:00 | 0 |
14:00 | 12:00 | 0 |
14:00 | 13:00 | 0 |
14:00 | 14:00 | 0 |
14:00 | 15:00 | 0 |
14:00 | 16:00 | 10 |
14:00 | 17:00 | 0 |
14:00 | 18:00 | 18 |
14:00 | 19:00 | 17 |
14:00 | 20:00 | 3 |
15:00 | 7:00 | 0 |
15:00 | 8:00 | 0 |
15:00 | 9:00 | 0 |
15:00 | 10:00 | 0 |
15:00 | 11:00 | 0 |
15:00 | 12:00 | 0 |
15:00 | 13:00 | 0 |
15:00 | 14:00 | 0 |
15:00 | 15:00 | 0 |
15:00 | 16:00 | 0 |
15:00 | 17:00 | 10 |
15:00 | 18:00 | 4 |
15:00 | 19:00 | 32 |
15:00 | 20:00 | 2 |
16:00 | 7:00 | 0 |
16:00 | 8:00 | 0 |
16:00 | 9:00 | 0 |
16:00 | 10:00 | 0 |
16:00 | 11:00 | 0 |
16:00 | 12:00 | 0 |
16:00 | 13:00 | 0 |
16:00 | 14:00 | 0 |
16:00 | 15:00 | 0 |
16:00 | 16:00 | 0 |
16:00 | 17:00 | 0 |
16:00 | 18:00 | 5 |
16:00 | 19:00 | 4 |
16:00 | 20:00 | 11 |
17:00 | 7:00 | 0 |
17:00 | 8:00 | 0 |
17:00 | 9:00 | 0 |
17:00 | 10:00 | 0 |
17:00 | 11:00 | 0 |
17:00 | 12:00 | 0 |
17:00 | 13:00 | 0 |
17:00 | 14:00 | 0 |
17:00 | 15:00 | 0 |
17:00 | 16:00 | 0 |
17:00 | 17:00 | 0 |
17:00 | 18:00 | 0 |
17:00 | 19:00 | 2 |
17:00 | 20:00 | 3 |
18:00 | 7:00 | 0 |
18:00 | 8:00 | 0 |
18:00 | 9:00 | 0 |
18:00 | 10:00 | 0 |
18:00 | 11:00 | 0 |
18:00 | 12:00 | 0 |
18:00 | 13:00 | 0 |
18:00 | 14:00 | 0 |
18:00 | 15:00 | 0 |
18:00 | 16:00 | 0 |
18:00 | 17:00 | 0 |
18:00 | 18:00 | 0 |
18:00 | 19:00 | 0 |
18:00 | 20:00 | 10 |
19:00 | 7:00 | 0 |
19:00 | 8:00 | 0 |
19:00 | 9:00 | 0 |
19:00 | 10:00 | 0 |
19:00 | 11:00 | 0 |
19:00 | 12:00 | 0 |
19:00 | 13:00 | 0 |
19:00 | 14:00 | 0 |
19:00 | 15:00 | 0 |
19:00 | 16:00 | 0 |
19:00 | 17:00 | 0 |
19:00 | 18:00 | 0 |
19:00 | 19:00 | 0 |
19:00 | 20:00 | 0 |
20:00 | 7:00 | 0 |
20:00 | 8:00 | 0 |
20:00 | 9:00 | 0 |
20:00 | 10:00 | 0 |
20:00 | 11:00 | 0 |
20:00 | 12:00 | 0 |
20:00 | 13:00 | 0 |
20:00 | 14:00 | 0 |
20:00 | 15:00 | 0 |
20:00 | 16:00 | 0 |
20:00 | 17:00 | 0 |
20:00 | 18:00 | 0 |
20:00 | 19:00 | 0 |
20:00 | 20:00 | 0 |
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" ?
yes, 4 am placed time just means overnight or any day prior to the fulfillment date. I just group it all under 4 am for simplicity
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!
What is the priority? The orders placed at the 5am slot or the leftover orders from the 4am slot?
It doesn't really matter as long as we add the leftover orders to the next time slot.
The priority works vertically in the order orders are due. So at 4 am we were only able to fully the 7 and 8 am timeslots. Up next I care the most about the orders due at 9.
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)
Thank you, by the way, this is what I expect the math at 5 am to be like:
40 +5+101=146. 146-140=6.
Then, I would have 6 orders leftover (due at 10 am) that get carried out to the next time slot (6 am).
Yeah, this is back to my initial comment. Not something you can do in DAX as it has no concept of global variables.
Would it be ok to implement in Power Query? That would mean the result cannot be modified by filtering.
Power Query is not ideal but it should get the job done!
At the end of the day (pardon the pun) does it really matter when the order was placed? Ca we assume that your production starts at 4am and can handle up to 140 orders per hour until close at 8pm ?
Yes that's a fair point! It doesn't matter as far as priorities go.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |