Forum Discussion
MMZTM
3 years agoFrequent Visitor
Backwards scheduling
Hey guys, I am looking for an algorithm that will create a backward scheduling for me using the two tables shown below. Based on the delivery date of the order table, the completion dates for the r...
- 3 years ago
Hi, MMZTM load tables into PQ and try this code.
let order = Excel.CurrentWorkbook(){[Name="ORDER_TABLE"]}[Content], operation = Excel.CurrentWorkbook(){[Name="OPERATION_TABLE"]}[Content], order_rec = Record.FromList(order[DELIVERY], List.Transform(order[#"Order-No."], Text.From)), f = (t as table) => [days = List.Buffer(List.Transform(t[Time needed], (x) => Number.From(Splitter.SplitTextByDelimiter(" ")(x){0}))), finish = Record.Field(order_rec, Text.From(t[#"Operation-No."]{0})), gen = List.Generate( () => [i = 0, d = Record.Field(order_rec, Text.From(t[#"Operation-No."]{0}))], (x) => days{x[i]}? <> null, (x) => [i = x[i] + 1, d = Date.AddDays(x[d], - days{x[i]})], (x) => DateTime.Date(x[d]) ), out = Table.FromColumns(Table.ToColumns(t) & {gen}, Table.ColumnNames(t) & {"Completion until"})][out], op_gr = Table.Group(operation, {"Operation-No."}, {{"all", each f(Table.Sort(_, {"Job-No.", Order.Descending}))}}), z = Table.Combine(op_gr[all]), sorted = Table.Sort(z,{{"Operation-No.", Order.Ascending}, {"Job-No.", Order.Ascending}}) in sorted
MMZTM
3 years agoFrequent Visitor
Hey AlienSx,
awesome! Works great!
Thanks for the quick reply!
I would still have a basic question:
Would it be possible to write this logic based on the same data in DAX? An iterative solution to this problem seems to make the most sense, right?
If this is not possible I would be super grateful if you could show me an alternative DAX solution for this problem.