Forum Discussion

erhang's avatar
erhang
Helper II
8 years ago
Solved

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

 

 

 

 

Need Help
  • v-juanli-msft's avatar
    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

8 Replies

  • erhang's avatar
    erhang
    Helper II
    8 years ago

    İs there anybody , who can help me pls ?

     

    Thanks 

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      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