Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi dear Power BI Family ;
İ am happy to be here to take always support from you , so thanks in advance to everybody.
İ need your helps one more time to create a formula about below situation .
İ have two tables as below .
Table 1 : contains product , request delivery date, and requested quantities .
Table 2 : contains the stock quantities of the products.
As you see i have different delivery dates with different quantities.İ just want to distrubute the stock quantities based on delivery dates..İ 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
Solved! Go to Solution.
HI @erhang
Try this MEASURE
Distributed Stocks = VAR stock_to_distirubute = CALCULATE ( SUM ( Table2[Stock Qty] ), CROSSFILTER ( Table2[Product], Table1[Product], BOTH ) ) VAR Cumulativestock = CALCULATE ( SUM ( Table1[Requested Qty] ), FILTER ( ALLEXCEPT ( Table1, Table1[Product] ), Table1[Rquested Delivery Date] <= SELECTEDVALUE ( Table1[Rquested Delivery Date] ) ) ) VAR myqty = IF ( Cumulativestock > stock_to_distirubute, SELECTEDVALUE ( Table1[Requested Qty] ) - ( Cumulativestock - stock_to_distirubute ), SELECTEDVALUE ( Table1[Requested Qty] ) ) RETURN IF ( myqty < 0, 0, myqty )
Hi @erhang
To solve this situation, first add a calculated column as follows
Adjusted Delivery Date = Table1[Rquested Delivery Date] + Table1[Document Number] / 10000
Now you can revise the original measure by replacing Requested Delivery Date with Adjsuted Delivery Date
i.e.
Distributed Stocks = VAR stock_to_distirubute = CALCULATE ( SUM ( Table2[Stock Qty] ), CROSSFILTER ( Table2[Product], Table1[Product], BOTH ) ) VAR Cumulativestock = CALCULATE ( SUM ( Table1[Requested Qty] ), FILTER ( ALLEXCEPT ( Table1, Table1[Product] ), Table1[Adjusted Delivery Date] <= SELECTEDVALUE ( Table1[Adjusted Delivery Date] ) ) ) VAR myqty = IF ( Cumulativestock > stock_to_distirubute, SELECTEDVALUE ( Table1[Requested Qty] ) - ( Cumulativestock - stock_to_distirubute ), SELECTEDVALUE ( Table1[Requested Qty] ) ) RETURN IF ( myqty < 0, 0, myqty )
Hello,
Thanks for the answer to the original post. I wanted to know if it is possible to add a bit more complexity to the problem by adding another column to table 2.
Given the image below, and the algorithm already given, how can i include the idea that the stock is not already available at the beginning ? i receive a bit of stock every week (hence my week column in table 2).
Also, how can i simulate the fact that i might have to distribute the stock during two weeks because not enough stock (see example in grey )? How can i display the week when the distribution occured ?
Thanks a lot in advance.
HI @erhang
Try this MEASURE
Distributed Stocks = VAR stock_to_distirubute = CALCULATE ( SUM ( Table2[Stock Qty] ), CROSSFILTER ( Table2[Product], Table1[Product], BOTH ) ) VAR Cumulativestock = CALCULATE ( SUM ( Table1[Requested Qty] ), FILTER ( ALLEXCEPT ( Table1, Table1[Product] ), Table1[Rquested Delivery Date] <= SELECTEDVALUE ( Table1[Rquested Delivery Date] ) ) ) VAR myqty = IF ( Cumulativestock > stock_to_distirubute, SELECTEDVALUE ( Table1[Requested Qty] ) - ( Cumulativestock - stock_to_distirubute ), SELECTEDVALUE ( Table1[Requested Qty] ) ) RETURN IF ( myqty < 0, 0, myqty )
Please see attached file
Thank you very very much dear muhammed .
Hi there
İ forgot to tell you that sometimes "requested delivery date" can be same day. also the products are different.
when it is same day , the formula that our friend gave me last day on above is not working.
So i added new column to my sample "Document Number "
is it possible to work this formula in this rule pls ?
when the requested delivery days are different , the distribution will be based on day as we designed firstly as above .
But when the requested delivery dates are same , formula will check document number , and will distrubate based on smallest document number .
Note : Document number can be smaller also it has the latest requested delivery date .
İ hope i could told you dear friends
Thanks for your supports
New Table1
Table2
Hi there ,
is there anybody who can help about this issue.
i can not use the formula that was given to me when the "requested delivery dates " are same .So for second condition i want use Document numbers .
Hi @erhang
To solve this situation, first add a calculated column as follows
Adjusted Delivery Date = Table1[Rquested Delivery Date] + Table1[Document Number] / 10000
Now you can revise the original measure by replacing Requested Delivery Date with Adjsuted Delivery Date
i.e.
Distributed Stocks = VAR stock_to_distirubute = CALCULATE ( SUM ( Table2[Stock Qty] ), CROSSFILTER ( Table2[Product], Table1[Product], BOTH ) ) VAR Cumulativestock = CALCULATE ( SUM ( Table1[Requested Qty] ), FILTER ( ALLEXCEPT ( Table1, Table1[Product] ), Table1[Adjusted Delivery Date] <= SELECTEDVALUE ( Table1[Adjusted Delivery Date] ) ) ) VAR myqty = IF ( Cumulativestock > stock_to_distirubute, SELECTEDVALUE ( Table1[Requested Qty] ) - ( Cumulativestock - stock_to_distirubute ), SELECTEDVALUE ( Table1[Requested Qty] ) ) RETURN IF ( myqty < 0, 0, myqty )
Please see attached file
dear @Zubair_Muhammad ;
İ noticed that sometimes in my report , "document number" and "request delivery date" can be same too.
but when it happens your formula is not distributing .
in below example ;
how can we solve this situation , thanks for your supports
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
96 | |
89 | |
73 | |
61 | |
58 |