Dax formula Help

[erhang]

Hi dear Power BI Family ;

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

İ have two tables as below  .

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

Table 2 : contains the stock quantities of the products.

As you see i have delivery dates with different quantities.İ just want to distrubute the stock quantities (Table2)  based on delivery dates.

and if the delivery date is same for each some products ,  i want that system will check document number and will give the stock firstly the product which document number is smaller .But system will never care document number if the delivery times are different while distrubuting.

İ 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 

[erhang]

İs there anybody , who can help me pls ?

Thanks 

[v-juanli-msft · Accepted solution]

Hi @erhang

I can get this result with the help of formula below

stock qty =
RELATED ( Sheet1[stock qty] )

rank =
VAR rank1 =
    RANKX ( ALLEXCEPT ( Sheet2, Sheet2[product] ), [requested delivery day],, ASC )
VAR rankcondition =
    IF (
        CALCULATE (
            COUNT ( Sheet2[document number] ),
            FILTER (
                ALLEXCEPT ( Sheet2, Sheet2[product] ),
                [requested delivery day] = EARLIER ( Sheet2[requested delivery day] )
            )
        )
            >= 2,
        RANKX (
            FILTER (
                ALLEXCEPT ( Sheet2, Sheet2[product] ),
                [requested delivery day] = EARLIER ( Sheet2[requested delivery day] )
            ),
            [document number],
            ,
            ASC
        )
    )
RETURN
    IF ( rankcondition = 2, rank1 + 1, rank1 )

residue =
VAR cumulativesum =
    CALCULATE (
        SUM ( Sheet2[requested qty] ),
        FILTER ( Sheet2, [rank] <= EARLIER ( [rank] ) )
    )
RETURN
    [stock qty] - cumulativesum

flag =
IF (
    [residue] >= 0,
    1,
    IF ( [residue] < 0 && [residue] > MIN ( [residue] ), 0, -1 )
)

distrubutedstocks =
VAR finalresidue =
    CALCULATE ( MIN ( [residue] ), FILTER ( Sheet2, [flag] = 1 ) )
RETURN
    IF ( [flag] = 1, [requested qty], IF ( [flag] = 0, finalresidue, 0 ) )

Best Regards

Maggie

[erhang]

dear  @v-juanli-msft ;

thanks for your helps 

Erhan 

[erhang]

dear @v-juanli-msft ;

is it possible to send me pbix file of these formula , because when i try to use it on power bi some formulas are not working 😞

[v-juanli-msft]

Hi @erhang

Here is my pbix.

Sheet1 show every distinct step, Sheet2 show the steps calculated by the formula i provided before.

Best Regards

Maggie

[erhang]

dear @v-juanli-msft ;

Thanks for your reply and help 

İ am mixed a little bit , in your pbix file there are 2 tables ( sheet1 and sheet2) do i have to use both of them ?

İ just opened a new file and tried to use your formulas , also just i copied from your file and i got below message on "rank column" 

i did not create any measure or formula on sheet1 , just i thought that with the formulas in sheet2 i can  calculate what i wanted .

so do i need to calculate as your sample pbix file or why did i take this error ? con you give me some advice pls ?

thank you very much.

Erhan 

[erhang]

dear @v-juanli-msft ;

i created your " every distinct step" explanation and "distrubuted stock " worked  but i noticed that when i added more product and different product calculating become wrongly.you can see as in below picture .

infact i just want the final situation as below , we are closer to final for just only one product type it is ok but when i add the second product type to the lines (in column) system is nat calculating , i hope it is clear and we can do it , thanks for your supports dear friend 

[v-juanli-msft]

Hello @erhang

Sorry for replying so late.

After reviewing and testing, I modify my original pbix to a clear one which takes two different products into consideration.

Here is my modified pbix.

Best regards

Maggie