Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.