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.

     

     

     

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

 

You have multiple references for the same TRANDATE. Neither Power Query nor Power BI guarantees a sort order. You must provide your own index column.

 

What should happen when a fitting is attempted while no stock is available?

 

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