Help for supply chain minimum quantity requirement

[Manoli21]

Hello!

I have some difficulties to create the good formula..

My case is this one :

I have some materials which need to have always at a special storage location a minimum of stock quantity (in A01)

If the stock is not covered the minimum quantity. I need to know how many pallets I need toi supply.

When I know how many pallets I need to supply. I check in another storage location how many pallet sI have.

If I have enough quantity in pallet in another storage location, I need to select the batch I need by its oldest creation date (FIFO).

If there are differents lines for the same batch, I just need the batch number. If one batch is not enough to cover my minimum stock, I need to show a 2 batch number until I get enough stock.

For examples in my power BI in attached, for the item 1200952, I have 90 000 pcs in stock in A01 which is not enough because my minimum stock is 145 000 pcs. So I need 55 000 pcs more.

If i check my stock in storage type A02, I see that there is 150 000 pcs.

In the table in my power bi, i can see the details of what I have in stock in A02 per batches.

Here what I would need, it's to show in my first table, in a last column the batches I need to succeed having minimum stock. So it would be batch FA819809/FA819810/ FA819811 in concatenate.

Here is the link of my power bi

https://we.tl/t-ieAjEqhimo 

Thank you very much for your help

Manoli

[v-kongfanf-msft · Accepted solution]

Hi  @Manoli21 ,

Maybe you can try formula like below to create measure:

Deficit = 
VAR CurrentStock =
    CALCULATE (
        SUM ( 'Stock'[Quantity] ),
        'Stock'[Storage Location] = "A01"
    )
VAR MinimumStock =
    LOOKUPVALUE (
        'Mini'[Minimum Quantity],
        'Mini'[Item], MAX ( 'Stock'[Item] )
    )
RETURN
    MAX ( 0, MinimumStock - CurrentStock )

Batches = 
VAR RequiredStock = [Deficit]
VAR BatchTable =
    FILTER (
        'Stock',
        'Stock'[Storage Location] = "A02"
            && 'Stock'[Item] = MAX(  'Stock'[Item] )
    )
VAR SortedBatchTable =
    ADDCOLUMNS (
        BatchTable,
        "CumulativeStock",
            SUMX (
                FILTER ( BatchTable, [Creation Date] <= EARLIER ([Creation Date] ) ),
                [Quantity]
            )
    )
VAR SelectedBatches =
    CONCATENATEX (
        FILTER ( SortedBatchTable, [CumulativeStock] <= RequiredStock ),
        [Batch],
        "/",
        [Creation Date], ASC
    )
RETURN
    SelectedBatches

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Manoli21]

Hello Adamk

Thank you very much for your proposal!

But I would change the formula like that :

Batches =

VAR RequiredStock = [Deficit]

VAR BatchTable =

    FILTER (

        'Stock',

        'Stock'[Storage Location] = "A02"

            && 'Stock'[Item] = MAX(  'Stock'[Item] )

    )

VAR SortedBatchTable =

    ADDCOLUMNS (

        BatchTable,

        "CumulativeStock",

            SUMX (

                FILTER ( BatchTable, [Creation Date] <= EARLIER ([Creation Date] ) ),

                [Quantity]

            )

    )

VAR SelectedBatches =

    CONCATENATEX (

        FILTER ( SortedBatchTable, [CumulativeStock] >= RequiredStock ),

        [Batch],

        "/",

        [Creation Date], ASC

    )

RETURN

    SelectedBatches

Your formula is showing me a quantity of batch quantity which is inferior to my "deficit" and I need the oposite.

If I change the sign to >= instead of <= the formula does not work complety because I only need to see enough batches by the oldest which can cover at a minimum the quantity in "deficit".

So in reality I would need to see batch FA819809 because of his age (03/02/2024) quantity = 45 000 pcs + FA819810 of 5000 pcs (another batch because we miss 10 000 pcs to have get quantity of 55 000 pcs(in deficit) and his age)

+ FA819811 (55 000 pcs). So I would need to see FA819809 FA819810 FA819811. In quantity is 60 000 pcs, superior of the deficit of 55 000 pcs

Do you have another idea ? 😄

https://we.tl/t-wDzbSs7qZx 

Thank you !

[Manoli21]

Hello @lbendlin 

Thank you for your answer

I agree with you but I have no choice, asked me to use Power BI..

Manoli

[lbendlin]

Power BI is a reporting tool. It is not suitable for inventory management. Please use a tool that was specifically designed for that purpose.