Calculate required wait time of material

[Anonymous]

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 

[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.

[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 

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 

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!

[Anonymous]

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
    Combined

If 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

[Anonymous]

Hi @v-karpurapud ,
I used the following code:

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}),
                    newRecord = if Record.HasFields(d{2}, v) then d{2} else Record.AddField(d{2}, v, a - d{0})
                in
                    {
                        d{0} - a,
                        d{1} & {{c{0}, Date.FromText(v), qtyUsed, days}},
                        newRecord
                    }
            ),
            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


--> The result is: 

The yellow lines are correct and the red x-lines are not correct. 

Here is the input and the expected result i want:
Input:

expected result:

[v-karpurapud · Accepted solution]

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!

[dufoq3]

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
    Result

[Anonymous]

Hi @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

[Cookistador]

Hello @Anonymous 

For your case, I would do it in many steps:

1. Create a new column if dispatch Quantity * -1 else Quantity
2. Sort your table by Product and date
3. Create two index columns one from 1 and the other from 0
4. Self inner join your table with Index0 & Product AND Index1 & Product
5. Expand your table to retrieve Quantity and Date and rename them with something like Prev Row Quantity and Prev Row Date
6. Clean you table
7. 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

[Anonymous]

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.