trying to extract a value from a many to many relationship

[CL7777]

Hi all,

I am an intermediate dax user but Im struggling to figure out how to do this.

I have a table (call it Sales) with part number and sale transaction date. I have a second table (call it Pricing) with a list of part numbers and MSRP selling prices with start dates and end dates. So, in this second table, I have part numbers listed several times with date ranges (start dates and end dates) for each new price that they have had over time. This creates a many to many relationship if I try to connect part number in the two tables.

What I want to do is create a calculated column in the Sales table that looks at the Pricing table and picks the MSRP for the date range which the transaction date is in. In other words, for a given line in the Sales table, take the part number and the transaction date, and look in the Pricing table for the part number and the start date and end date range that contains the transaction date and report back to the column the MSRP that from the Pricing table that meets that criterion. 

here is what I came up with, but it doesnt work

Column =

VAR

pricehistorytable = SUMMARIZE('_Part Price History', [Part Number], [Start Date], [End Date], [Unit Price])

VAR

abbreviatedTable =

ADDCOLUMNS(CALCULATETABLE(pricehistorytable, FILTER(pricehistorytable, '_Revenue All Component ss'[Part Number] = [Part Number])), "correctMSRPflag", IF([Transaction Date] > [Start Date] && [Transaction Date] < [End Date], "T", "F"))

RETURN

CALCULATE(MINX(abbreviatedTable, [Unit Price]), FILTER(abbreviatedTable, [correctMSRPflag] = "T"))

Am I on the right track? It doesnt work. Thanks for your help,

CL7777

[Anonymous · Accepted solution]

Here's what worked for me:

I have a Product Pricing table with ProductKey, StartDate, EndDate, and Price.

I have a Sales table that has ProductKey and Date.

In the Sales table I created the following column:

Price at Date =

VAR _ProductKey = Sales[ProductKey]

VAR _SaleDate = Sales[Date]

VAR _Return = CALCULATE(MIN('Product Pricing'[Price]), FILTER(ALL('Product Pricing'), 'Product Pricing'[ProductKey] = _ProductKey && 'Product Pricing'[StartDate] <= _SaleDate && 'Product Pricing'[EndDate] >= _SaleDate))

RETURN _Return

Seems to do the trick.

Hope that helps.

Eric

[CL7777]

thats perfect... thank you!!