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

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

     

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hello lbendlin 

    Thank you for your answer

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

    Manoli

  • 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 !