Forum Discussion

erhang's avatar
erhang
Helper II
8 years ago
Solved

dax formula help (Distribution)

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...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
Zubair_Muhammad
Community Champion
8 years ago

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 )
  • erhang's avatar
    erhang
    Helper II
    8 years ago

    dear Zubair_Muhammad; 

     

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

  • erhang's avatar
    erhang
    Helper II
    8 years ago

    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