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 )

[Astronomique]

Hello,

Thanks for the answer to the original post. I wanted to know if it is possible to add a bit more complexity to the problem by adding another column to table 2.

https://imgur.com/a/gHiCLm7 

Given the image below, and the algorithm already given, how can i include the idea that the stock is not already available at the beginning ? i receive a bit of stock every week (hence my week column in table 2).

Also, how can i simulate the fact that i might have to distribute the stock during two weeks  because not enough stock (see example in grey )? How can i display the week when the distribution occured ?

Thanks a lot in advance.

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

[Zubair_Muhammad]

@erhang

Please see attached file

[erhang]

Thank you very very much dear muhammed .

[erhang]

Hi there

İ forgot to tell you that sometimes "requested delivery date" can be same day. also the products are different.

when it is same day , the formula that our friend gave me last day on above is not working.

So i added new column to my sample "Document Number "

is it possible to work this formula in this rule pls ?

when the requested delivery days are different , the distribution will be based on day as we designed firstly as above .

But when the requested delivery dates are same , formula will check document number , and will distrubate based on  smallest document number .

Note : Document number can be smaller also it has the latest requested delivery date .

İ hope i could told  you dear friends

Thanks for your supports 

                                                                                                                                     New Table1

                   Table2 

[erhang]

Hi there ,

is there anybody who can help about this issue.

i can not use the formula that was given to me when the "requested delivery dates " are same .So for second condition i want use Document numbers .

[Zubair_Muhammad · Accepted solution]

Hi @erhang

To solve this situation, first add a calculated column as follows

Adjusted Delivery Date =
Table1[Rquested Delivery Date]
    + Table1[Document Number] / 10000

Now you can revise the original measure by replacing Requested Delivery Date with Adjsuted Delivery Date

i.e.

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[Adjusted Delivery Date]
                <= SELECTEDVALUE ( Table1[Adjusted 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 )

[Zubair_Muhammad]

@erhang

Please see attached file

[erhang]

dear @Zubair_Muhammad; 

Thanks for amazing solution .It is working as i want .

[erhang]

dear @Zubair_Muhammad ;

İ noticed that sometimes in my report ,  "document number" and "request delivery date" can be same too.

but when it happens your formula is not distributing . 

in below example  ; 

• İ have 130 pcs stock for product  "1" 
• but system stop to distribute when comes to same document number and same delivery date .
• Till 14.06.2018 , we need 125 spcs , ok it is distiributing but i have 130 pcs stock so it can give 5 pcs more the lines taht i marked yellow , but it does not  distirubate 

 how can we solve this situation , thanks for your supports