Forum Discussion
Many To Many Relation Issue
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,
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,
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.
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,