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

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

 

 

 

Capture10.JPG

 

                       

 

 

                   Table2 

 

Capture11.JPG

erhang's avatar
erhang
Helper II
8 years ago

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