Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
I am having problem with solving this scenario. I have multiple transaction of specific model per day . I want to allocate this sales quantity to import purchase quantity which will also have specific model with multiple day. My criteria is using date , product model and import declaration number. The following is an example of data and the result i want to get ;
Sales Table
Sales Date | Product Name | Quantity |
3/1/2022 | A | 30 |
5/1/2022 | B | 20 |
Purchase Table
Purchase Date | Import Declaration Number | Product Name | Quantity | Cost | Exchange Rate |
2/12/2022 | 100161238551 | A | 20 | 200 | 1.5 |
2/19/2022 | 100161238554 | A | 20 | 200 | 1.7 |
2/22/2022 | 100161238557 | A | 20 | 200 | 1.3 |
3/12/2022 | 100161238580 | B | 20 | 500 | 1.8 |
The Result i want to get
Purchase Date | Import Declaration Number | Product Model | Quantity | Cost | Exchange Rate | Sold | Closing Inventory |
2/12/2022 | 100161238551 | A | 20 | 200 | 1.5 | 20 | - |
2/19/2022 | 100161238554 | A | 20 | 200 | 1.7 | 10 | 10 |
2/22/2022 | 100161238557 | A | 20 | 200 | 1.3 | - | 20 |
3/12/2022 | 100161238580 | B | 20 | 500 | 1.8 | 20 | - |
Can someone help me? Also thanks in advance.
Hey @PhyuLayKhine333 ,
please explain the rule that leads to the expected result.
Regards,
Tom
My rule is like first in first out method using purchase date's declaration import number, purchase date criteria. Product A from purchase table which have different multiple import declaration number link with product A from sales table quantity and date criteria . The final i want to get is cost of sales and closing inventory of fifo method .
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
86 | |
78 | |
66 |
User | Count |
---|---|
153 | |
125 | |
114 | |
111 | |
95 |