The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
142 | |
109 | |
107 | |
76 | |
61 |
User | Count |
---|---|
276 | |
129 | |
124 | |
100 | |
89 |