Many To Many Relation Issue

[Poojara_D12]

Hello Members,

I have a scenario where I have a many to many relation issue which I have explained below, can anyone please help me with the solutions for the same?

As per above screenshot, I have 2 different tables : purchase and selling respetively. I have purchased similar products multiple time at a different purchase price. Both the table has QTY column. The QTY is been taken care of that if I have QTY left with the old purchase, I'll have to sell it first and then the other with HIGHER Purchase Price. 
The question is that if I want to find the selling price dynamically that what will be the way for it. For example, I got a order for 3 Iphone 13,and I'm left with 1 Iphone 13 whose Purchase Price is 150 and other 2 is the new one i.e.160. So How will I figure the P&L in this senario? PLEASE HELP. @Dangar332 @FreemanZ @shafiz_p @SachinNandanwar @Jihwan_Kim @lbendlin 

[DataNinja777]

Hi @Poojara_D12 ,

I would start by creating a dimension table for the product (iPhone) and establishing a one-to-many relationship with your purchase and sales fact tables, rather than using a many-to-many relationship between two fact tables 😉.

Next, I’d like to confirm whether you have separate columns for purchase date and sales date. Your P&L will recognize the cost of goods sold (COGS) at the point of revenue recognition (when sales are made), as inventory (assets) remain on the balance sheet until sales are made.

Please let us know if there is a separate date field for the sales and purchase fact tables.

Best regards,

[Poojara_D12]

Hi @DataNinja777 
There is no seperate date field for the sales and purchase given from client, also there is no Lot Number column in sales or purchase table as well.

[DataNinja777 · Accepted solution]

Hi @Poojara_D12 ,

To calculate the P&L, as outlined in your requirements, we need to determine the timing of item sales relative to purchases. Since your sold quantities are lower than your purchase quantities, this results in an inventory balance at the end of the transactions above. There are various methods to value this inventory, including FIFO (First In, First Out), LIFO (Last In, First Out), and moving average. Each of these valuation methods requires a reference to the time dimension.

Best regards,