topic Re: Many To Many Relation Issue in DAX Commands and Tips

Many To Many Relation Issue

Poojara_D12 — Tue, 05 Nov 2024 12:35:16 GMT

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

Re: Many To Many Relation Issue

DataNinja777 — Tue, 05 Nov 2024 14:38:17 GMT

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,

Re: Many To Many Relation Issue

Poojara_D12 — Wed, 06 Nov 2024 04:33:08 GMT

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.

Re: Many To Many Relation Issue

DataNinja777 — Wed, 06 Nov 2024 11:24:01 GMT

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,