Forum Discussion

erhan_79's avatar
erhan_79
Post Prodigy
5 years ago
Solved

stock distribution help

Hi there ;

 

i need your kind support about below issue pls .

 

i have two table "TableA" and "TableB " as below.TableA is related with orders , and TableB is giving stock quantities that i have for materials. I would like to distrubute stock quantities based on some rules to order quantities .Rules that i want to create like this ;

 

  • If even one of the request  date line in the one order is after this actual month . Dont Distrubute any stock ( as you see in the table for  "Order2 " , there is any distrubated stock qty because one of the line's delivery date  is after actual month (not  January 2021) yellow marked .So system elemanited "Order2" for calculation .
  • For distribution calculation just consider that : delivery date must be actual month and before ( January 2020 and /or before January 2021 )
  • Start to distribution from order which has the less total qty and then continue to calculate  second less total qty etc. match the materials with stock quantity based on same materials.As you see for below example system started to distribution from "Order3 " , it has totaly 15 pcs total Qty , then for second calculation to distrubate is for "Order4" with the 30 pcs total order qty , and finally third one "Order1"  with the 90 pcs total qty.Here i want to mention that as you see total stock qty is not enough for last calculated order ( Order1) , some materials can not available from stock.

Also i would like to share with you ready excel tables with below link for your calculation try to make easier.

 

https://drive.google.com/file/d/10Ju-bzTQo4Dqs1DTdUR_NOHke1UaHaJy/view?usp=sharing

 
 

 

thanks in advance

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi erhan_79 

     

    I add an index column in Table A to help distribute orders. And add a rule that if the remaining stock of a material is 0, then distribute 0 to the same material in next orders. Here is the PBIX file.

    Index = RANKX(TableA,CALCULATE(SUM(TableA[Order Qty]),ALLEXCEPT(TableA,TableA[Order Number])),,ASC,Dense)
    Distributed Stock Qty = 
VAR _t =
    FILTER (
        SUMMARIZE (
            TableA,
            TableA[Order Number],
            "MaxOrderDate",
                CALCULATE (
                    MAX ( TableA[Request  Date] ),
                    ALLEXCEPT ( TableA, TableA[Order Number] )
                ),
            "Total Qty", SUM ( TableA[Order Qty] )
        ),
        [MaxOrderDate] <= EOMONTH ( TODAY (), 0 )
    )
VAR orders =
    SELECTCOLUMNS ( _t, "Order Number", [Order Number] )
RETURN
    IF (
        NOT ( TableA[Order Number] IN orders ),
        0,
        VAR _m = TableA[Material]
        VAR _i = TableA[Index]
        VAR modifiedS =
            CALCULATE ( MAX ( TableB[Stock Qty] ), TableB[Material] = _m )
                - CALCULATE (
                    SUM ( TableA[Order Qty] ),
                    ALLEXCEPT ( TableA, TableA[Material] ),
                    TableA[Order Number] IN orders,
                    TableA[Index] < _i
                )
        VAR modifiedStock =
            IF ( modifiedS <= 0, 0, modifiedS )
        RETURN
            IF ( modifiedStock > TableA[Order Qty], TableA[Order Qty], modifiedStock )
    )

    Kindly let me know if this helps.
    Community Support Team _ Jing Zhang
    If this post helps, please consider Accept it as the solution to help other members find it.

5 Replies

    • erhan_79's avatar
      erhan_79
      Post Prodigy
      5 years ago

      hi dear @v-jingzhang  ;

       i want a calculated column that  mentioned in Table A at the end of right column i want  ,  table B ( stock quantities ) quantities will be distrubuted to Table A . kind of Stock matching llike , but just it has some rules , i will be so grateful if you would help

       

      thanks in advance 

      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        5 years ago

        Hi erhan_79 

         

        I add an index column in Table A to help distribute orders. And add a rule that if the remaining stock of a material is 0, then distribute 0 to the same material in next orders. Here is the PBIX file.

        Index = RANKX(TableA,CALCULATE(SUM(TableA[Order Qty]),ALLEXCEPT(TableA,TableA[Order Number])),,ASC,Dense)
        Distributed Stock Qty = 
VAR _t =
    FILTER (
        SUMMARIZE (
            TableA,
            TableA[Order Number],
            "MaxOrderDate",
                CALCULATE (
                    MAX ( TableA[Request  Date] ),
                    ALLEXCEPT ( TableA, TableA[Order Number] )
                ),
            "Total Qty", SUM ( TableA[Order Qty] )
        ),
        [MaxOrderDate] <= EOMONTH ( TODAY (), 0 )
    )
VAR orders =
    SELECTCOLUMNS ( _t, "Order Number", [Order Number] )
RETURN
    IF (
        NOT ( TableA[Order Number] IN orders ),
        0,
        VAR _m = TableA[Material]
        VAR _i = TableA[Index]
        VAR modifiedS =
            CALCULATE ( MAX ( TableB[Stock Qty] ), TableB[Material] = _m )
                - CALCULATE (
                    SUM ( TableA[Order Qty] ),
                    ALLEXCEPT ( TableA, TableA[Material] ),
                    TableA[Order Number] IN orders,
                    TableA[Index] < _i
                )
        VAR modifiedStock =
            IF ( modifiedS <= 0, 0, modifiedS )
        RETURN
            IF ( modifiedStock > TableA[Order Qty], TableA[Order Qty], modifiedStock )
    )

        Kindly let me know if this helps.
        Community Support Team _ Jing Zhang
        If this post helps, please consider Accept it as the solution to help other members find it.

  • erhan_79's avatar
    erhan_79
    Post Prodigy
    5 years ago

    hi dear v-jingzhang  ;

     i want a calculated column that  mentioned in Table A at the end of right column i want  ,  table B ( stock quantities ) quantities will be distrubuted to Table A . kind of Stock matching llike , but just it has some rules , i will be so grateful if you would help , thanks in advance