Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Allocate correct PO from the Stock Table based on stock availability

Good afternoon!   I have two tables in Power BI: 1. Stock Table: This table contains details of stock orders, including the Purchase Order (PO) number, the quantity ordered, and the transaction dat...
Need Help
Show and Tell
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    2 years ago

    Note: MDX2 has a higher index than MDX3.

     

     

    This approach breaks down/will be unreliable when you dip below zero stock.

     

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Thank you lbendlin. Yes, I have to use an index column. And in my calculation in Dax I am using that, but still don't get the correct allocation.

 

Good point that I have to deal with the situation there is no stock..

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Please see another example below:
    Stock table:

    REFERENCETRANDATESum of QUANTITYIndexCumulativeQuantity
    MDX102/01/2024111
    MDX212/04/2024234
    MDX316/04/2024122
    MDX417/04/20247411
    MDX517/06/20242513
    MDX617/06/20242615
    MDX718/06/20241716
    MDX803/07/20246822
    MDX904/07/20241923
    MDX1022/07/202421025

     

    Fitted Table, With The Expected PO Reference (allocating the available stock based on Qty available and Tran Date prior to the Call Date):

    Call NoSum of FitIndex1Call DateCumulativeFittedPOReference
    420X11112/04/20241MDX1
    420X21228/05/20242MDX2
    420X31328/05/20243MDX2
    420X41428/05/20244MDX3
    420X51528/05/20245MDX4
    420X61629/05/20246MDX4
    420X71729/05/20247MDX4
    420X81829/05/20248MDX4
    420X91921/06/20249MDX4
    420X1011021/06/202410MDX4
    420X1111121/06/202411MDX4
    420X1211221/06/202412MDX5
    420X1311321/06/202413MDX5
    420X1411421/06/202414MDX6
    420X1511521/06/202415MDX6
    420X1611628/06/202416MDX7
    420X1711701/07/202417MDX8
    420X1811803/07/202418MDX8
    420X1911903/07/202419MDX8
    420X2012005/07/202420MDX8

     

    Please let me know if you have any questions. Thank you for looking into this!!

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      Note: MDX2 has a higher index than MDX3.

       

       

      This approach breaks down/will be unreliable when you dip below zero stock.

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Thank you very much. It looks that you've achieved the correct allocation. So will test your solution, sounds that as long as there is always stock this will work fine.

         

        Many thanks for your help!!