Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
97 | |
85 | |
70 | |
67 |
User | Count |
---|---|
116 | |
109 | |
94 | |
79 | |
72 |