Forum Discussion
Calculate required wait time of material
Hi all,
I have the following problem. I want to caluclate some required wait time of some material.
The data shows the receipt of some qty material.
The data shows the dispatch of some qty material.
For each transaction is one row available. (1)
Is there some solution to calculate the Days (2) in the example?
If you have any solution it would be great if you can help me.
1) Data that is available
2)Result that is needed for caluclation.
BR
Hi Anonymous
Please try the below updated DAX:let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText("i45WMjDUAyIjAyNTJR0lE3NDQyBVlJqcmllQAmQZGhgoxeoAVZliqkrJLC5ILEnOADJNIaoMDfCqAgrjUoVpoyFRNhoYoaoyQjHLCOZ6M0xVyO4CKYsFAA==", BinaryEncoding.Base64), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Booking = _t, Qty = _t] ), ChangedType = Table.TransformColumns(Source, { {"Date", each Date.FromText(_, "de-DE"), type date}, {"Product", each Int64.From(_), Int64.Type}, {"Booking", each _, type text}, {"Qty", each Int64.From(_), Int64.Type} }), Normalized = Table.ReplaceValue(ChangedType, each [Booking], each if [Booking] = "Eingang" then "receipt" else if [Booking] = "Ausgang" then "dispatch" else [Booking], Replacer.ReplaceValue, {"Booking"}), Grouped = Table.Group(Normalized, {"Product"}, { {"Result", (grpTable) => let DispatchMap = Function.Invoke( Record.FromList, Table.ToColumns( Table.TransformColumns( Table.SelectRows(grpTable, each [Booking] = "dispatch")[[Qty], [Date]], {{"Date", each Date.ToText(_, "yyyy-MM-dd")}} ) ) ), Gen = List.Accumulate( Table.ToRows(Table.SelectRows(grpTable, each [Booking] = "receipt")[[Date], [Qty]]), {{}, DispatchMap, Record.FieldNames(DispatchMap)}, (s, c) => [ la = List.Accumulate(s{2}, {c{1}, {}, s{1}}, (d, v) => let a = Record.Field(d{2}, v), qtyUsed = List.Min({a, d{0}}), newRemainingQty = a - qtyUsed, updatedDispatchMap = if newRemainingQty > 0 then Record.TransformFields(d{2}, {{v, each newRemainingQty}}) else Record.RemoveFields(d{2}, v) in { d{0} - qtyUsed, d{1} & {{c{0}, Date.FromText(v), qtyUsed, Duration.Days(Date.FromText(v) - c{0})}}, updatedDispatchMap } ), result = {s{0} & la{1}, la{2}, Record.FieldNames(la{2})} ][result] ), Output = Table.FromRows(Gen{0}, type table [Receipt = date, Booking = date, Qty = number, Days = number]), WithProduct = Table.AddColumn(Output, "Product", each grpTable{0}[Product]) in WithProduct } }), Combined = Table.Combine(Grouped[Result]) in Combined
I have attached a snapshot for reference:
I hope this meets your requirement. If this post helps , kindly, mark it as Accepted Solution.
Thank You!
13 Replies
- Cookistador
Super User
Hello Anonymous
For your case, I would do it in many steps:
- Create a new column if dispatch Quantity * -1 else Quantity
- Sort your table by Product and date
- Create two index columns one from 1 and the other from 0
- Self inner join your table with Index0 & Product AND Index1 & Product
- Expand your table to retrieve Quantity and Date and rename them with something like Prev Row Quantity and Prev Row Date
- Clean you table
- Make the calculation directly in Power Query or in dax (substraction and Datediff)
If it is not clear, do not hesistate to ask for me details
Hi Cookistador ,
thank you for youre reply. The problem is, that the calculation is needed in some cases for 2 lines. For example:
100 pcs are receipt and 50 pcs are dispatched.
Then there is some receipt over 100 pcs again.
The next receipts must be subract from the first 100 pcs.
- dufoq3
Community Champion
Hi Anonymous, this will work if you have your table sorted by Date and receipts are greater than bookings so far...
Output
let Source = Table.FromColumns({{#date(2025,1,1), #date(2025,1,5), #date(2025,1,10), #date(2025,1,10), #date(2025,1,15)}, List.Repeat({4711}, 5), {"receipt", "dispatch", "dispatch", "receipt", "dispatch"}, {100,50,25,100,50}}, type table[Date=date, Product=Int64.Type, Booking=text, Qty=number]), D = Function.Invoke(Record.FromList, Table.ToColumns(Table.TransformColumns(Table.SelectRows(Source, each ([Booking] = "dispatch"))[[Qty], [Date]], {{"Date", each Date.ToText(_, [Format="yyyy-MM-dd"])}}))), Gen = List.Accumulate(Table.ToRows(Table.SelectRows(Source, each [Booking] = "receipt")[[Date], [Qty]]), {{}, D, Record.FieldNames(D)}, (s,c)=> [ la = List.Accumulate(s{2}, {c{1}, {}, s{1}}, (d,v)=> let a = Record.Field(s{1}, v) in { d{0} - a, d{1} & {{c{0}, Date.FromText(v), List.Max({0, if d{0} > a then a else a - d{0}}), Duration.Days(Date.FromText(v) - c{0})}}, if d{0} > a then Record.RemoveFields(d{2}, v) else d{2} & Record.AddField([], v, a - d{0}) }), result = { s{0} & la{1}, la{2}, Record.FieldNames(la{2}) } ][result]), Result = Table.FromRows(Gen{0}, type table[Receipt=date, Booking=date, Qty=number, Days=Int64.Type]) in ResultHi dufoq3,
thank you for reply. Is it although working if I have more than one material in the table?
So with more than one material in the table?
BR Sebastian- dufoq3
let Source = Table.FromColumns({{#date(2025,1,1), #date(2025,1,5), #date(2025,1,10), #date(2025,1,10), #date(2025,1,15)}, List.Repeat({4711}, 5), {"receipt", "dispatch", "dispatch", "receipt", "dispatch"}, {100,50,25,100,50}}, type table[Date=date, Product=Int64.Type, Booking=text, Qty=number]), Transformed = Table.Combine(Table.Group(Source, "Product", {{"T", each [ D = Function.Invoke(Record.FromList, Table.ToColumns(Table.TransformColumns(Table.SelectRows(_, each ([Booking] = "dispatch"))[[Qty], [Date]], {{"Date", each Date.ToText(_, [Format="yyyy-MM-dd"])}}))), Gen = List.Accumulate(Table.ToRows(Table.SelectRows(_, each [Booking] = "receipt")[[Date], [Qty]]), {{}, D, Record.FieldNames(D)}, (s,c)=> [ la = List.Accumulate(s{2}, {c{1}, {}, s{1}}, (d,v)=> let a = Record.Field(s{1}, v) in { d{0} - a, d{1} & {{c{0}, Date.FromText(v), List.Max({0, if d{0} > a then a else a - d{0}}), Duration.Days(Date.FromText(v) - c{0})}}, if d{0} > a then Record.RemoveFields(d{2}, v) else d{2} & Record.AddField([], v, a - d{0}) }), result = { s{0} & la{1}, la{2}, Record.FieldNames(la{2}) } ][result]), Result = Table.FromRows(Gen{0}, type table[Receipt=date, Booking=date, Qty=number, Days=Int64.Type]) ][Result], type table}})[T]) in Transformed
v-karpurapudCommunity Support
Hi Anonymous
The solution provided by dufoq3 is accurate. By using Table.Group and List.Accumulate, it ensures that each material’s receipts and dispatches are properly matched, and the waiting time in days is calculated correctly. Could you please confirm if your query has been resolved? If so, kindly mark the helpful response and accept it as the solution. This will help other community members in resolving similar issues more efficiently. If not, please provide detailed information so we can assist you better.
Thank you!
Hi v-karpurapud , no sorry. The solution from dufoq3 is not working.
- v-karpurapud
Hi Anonymous
Thank you for your response.
Please try the updated Power Query M code below, which fully supports multiple materials and ensures accurate FIFO matching between receipts and dispatches:
let Source = Table.FromColumns( { {#date(2025,1,1), #date(2025,1,5), #date(2025,1,10), #date(2025,1,10), #date(2025,1,15), #date(2025,1,2), #date(2025,1,6)}, {4711, 4711, 4711, 4711, 4711, 4712, 4712}, {"receipt", "dispatch", "dispatch", "receipt", "dispatch", "receipt", "dispatch"}, {100, 50, 25, 100, 50, 200, 200} }, type table [Date=date, Product=Int64.Type, Booking=text, Qty=number] ), Grouped = Table.Group(Source, {"Product"}, { {"Result", (grpTable) => let DispatchMap = Function.Invoke(Record.FromList, Table.ToColumns( Table.TransformColumns( Table.SelectRows(grpTable, each [Booking] = "dispatch")[[Qty], [Date]], {{"Date", each Date.ToText(_, "yyyy-MM-dd")}} ) ) ), Gen = List.Accumulate( Table.ToRows(Table.SelectRows(grpTable, each [Booking] = "receipt")[[Date], [Qty]]), {{}, DispatchMap, Record.FieldNames(DispatchMap)}, (s, c) => [ la = List.Accumulate(s{2}, {c{1}, {}, s{1}}, (d, v) => let a = Record.Field(s{1}, v), qtyUsed = List.Max({0, if d{0} > a then a else a - d{0}}), days = Duration.Days(Date.FromText(v) - c{0}) in { d{0} - a, d{1} & {{c{0}, Date.FromText(v), qtyUsed, days}}, if d{0} > a then Record.RemoveFields(d{2}, v) else Record.AddField(d{2}, v, a - d{0}) } ), result = { s{0} & la{1}, la{2}, Record.FieldNames(la{2}) } ][result] ), Output = Table.FromRows(Gen{0}, type table [Receipt=date, Booking=date, Qty=number, Days=number]), WithProduct = Table.AddColumn(Output, "Product", each grpTable{0}[Product]) in WithProduct } }), Combined = Table.Combine(Grouped[Result]) in CombinedIf you're still facing issues, please share a sample of your actual dataset, any errors you're encountering, or specific cases where the output appears incorrect, so we can assist you more effectively.
Best regards,
karpurapud
- v-karpurapud
Hi Anonymous
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- v-karpurapud
Hi Anonymous
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you. - v-karpurapud
Hi Anonymous
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
