Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 mate...
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    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!

v-karpurapud's avatar
v-karpurapud
Icon for Community Support rankCommunity Support
1 year ago

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's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    v-karpurapud
    Icon for Community Support rankCommunity Support
    1 year ago

    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!