cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhyuLayKhine333
Frequent Visitor

Allocate sales quantity to import purchase quantity

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 DateProduct NameQuantity
3/1/2022A30
5/1/2022B20

 

Purchase Table

Purchase DateImport Declaration NumberProduct NameQuantityCostExchange Rate
2/12/2022100161238551A202001.5
2/19/2022100161238554A202001.7
2/22/2022100161238557A202001.3
3/12/2022100161238580B205001.8

 

The Result i want to get

Purchase DateImport Declaration NumberProduct ModelQuantityCostExchange RateSoldClosing Inventory
2/12/2022100161238551A202001.520-
2/19/2022100161238554A202001.71010
2/22/2022100161238557A202001.3-20
3/12/2022100161238580B205001.820-

 

Can someone help me? Also thanks in advance.

2 REPLIES 2
TomMartens
Super User
Super User

Hey @PhyuLayKhine333 ,

 

please explain the rule that leads to the expected result.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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 . 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors