dax formula help (Distribution)

[erhang]

Hi dear Power BI Family ;

İ am happy to be here to take always support from you  , so thanks in advance to everybody.

İ need your helps one more time to create a formula about below situation .

İ have two tables as below  .

Table 1 : contains product , request delivery date, and requested quantities .

Table 2 : contains the stock quantities of the products.

As you see i have different delivery dates with different quantities.İ just want to distrubute the stock quantities based on delivery dates..İ would like to show you to  step by step ( based on only one product ) .İ want to tell like that because the issue has some interesting details so just i want to tell correctly 

For Product PRC1 distribution: 

For Product PRC2 distribution: 

and the finally whole new table 1 will be as below : 

 I hope it is possible to make distrubition like that 

İf it is possible could you help me pls about this issue 

Thanks 

Erhan 

[Zubair_Muhammad · Accepted solution]

HI @erhang

Try this MEASURE

Distributed Stocks =
VAR stock_to_distirubute =
    CALCULATE (
        SUM ( Table2[Stock Qty] ),
        CROSSFILTER ( Table2[Product], Table1[Product], BOTH )
    )
VAR Cumulativestock =
    CALCULATE (
        SUM ( Table1[Requested Qty] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Product] ),
            Table1[Rquested Delivery Date]
                <= SELECTEDVALUE ( Table1[Rquested Delivery Date] )
        )
    )
VAR myqty =
    IF (
        Cumulativestock > stock_to_distirubute,
        SELECTEDVALUE ( Table1[Requested Qty] )
            - ( Cumulativestock - stock_to_distirubute ),
        SELECTEDVALUE ( Table1[Requested Qty] )
    )
RETURN
    IF ( myqty < 0, 0, myqty )