cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

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

 
 

Capture.JPG 

thanks in advance

 

1 ACCEPTED SOLUTION

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

012701.jpg

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.

View solution in original post

5 REPLIES 5
Post Prodigy
Post Prodigy

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 

Microsoft
Microsoft

Hi @erhan_79 , you want a calculated column in the table or a column shown in a table visual?

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 

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

012701.jpg

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.

View solution in original post

Dear @v-jingzhang  ;

 

it is working perfect ! Thank you very much for your kind support 

 

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors