Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Help for supply chain minimum quantity requirement

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 quantit...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi  Anonymous ,

     

    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.

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi  Anonymous ,

 

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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 ? 😄
     
     
    Thank you !