Calculate field combining two 2 fields? Cost by month

[AlexVieira]

Hello there!

I've been knocking my head in the wall over this one and i'm stucked

Here is the deal:
Have a data model with two tables:
A sales table with the "primary key" being the Item reference.
Another table with the product cost. The thing is it has the prices by month.

I'm looking to create a calculated field in the data model that relates the sales table with the product cost within the correct month.

I've tried to assemble the product cost in two ways:
One with three columns (Reference | Cost | Date) - Here the rows have the reference repeated for each month
Other with (Reference | Jan 2016 | Feb 2016 | etc |etc ) - Here no repetitions of the reference but the date is is the column header.

Any thoughts?

Thanks in advance!!

 

[Matt Allington]

A few thoughts from me. You say your sales table has a primary key - that is unlikely unless it is an invoice header table and the primary key is the invoice number. Is this instead a foreign key to a product? Assuming so, I would create a product table with a primary key being the product number. Link the sales table to this one. Then create a calendar table and link that to the sales table too. The price table should be as you suggest in your option 1, definitely not option 2. If your table has 1 price record per month per product, you should be good to go as long as your sales table is also monthly. Just join the price table to the product table and the calendar table. If the sale table is daily then you will need to do something a bit different, as it won't work to connect the monthly prices to the calendar day table. You would instead not join the price table to the calendar table, and need to write a more complex dax formula to filter the price table based on the selection in he calendar table. If you can post a sample as I describe, I can help you out.