Hello,
I have two fact tables, one with all the sales and customer data, and one which I am using to pull the COGS.
There is no direct link between the two tables for specific orders and customers as the intergration between the sales platform where the sales data comes from and the GL where the COGS data comes from has not been created., but the stores and SKUs are the same.
I want to be able to calculate the GM for each sales order. I created an inactive relationship between the two fact tables and USERELATIONSHIP in the COGS measure. This has stopped COGS appearing for all SKUs/customers when not in the order. However, there are some instances when the invoice dates are different ,so there is no COGS data for that store and month and SKU.
If I remove the date filter using ALL, then values are returned for months with no orders, likewise if I use an average. HOw can I create a COGS pu measure that I can use when using columns from the Sales table? The ideal result would be the COGS PU US RELATIONSHIP MONTH table with out any blank COGS.
COGS TEST HB.pbix (106.3 KB)