Dax formula Help

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 , reques...

Need Help

v-juanli-msft

8 years ago

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

Helper II

8 years ago

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