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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.