Get value from another table using intermediary table

[jjolk]

I'm using the Microsoft provided Adventureworks data for SQL Server in a Power BI project.  I want to get the unit cost of a product but it requires three tables.  I was going to create a new column in the SalesOrderDetail to calculate the UnitCost by getting the StandardCost during the correct period (StartDate, EndDate) from the ProductCostHistory table.  But I don't know how to reference all three tables.   

I need the SalesOrderHeader for the OrderDate and the SalesOrderDetail for the ProductID so that I get the correct historical cost.  

Below is the SQL query which gives me the proper relationships. 

SELECT SOD.UnitPrice, SOH.OrderDate, PCH.StandardCost, PCH.StartDate, PCH.EndDate, PCH.ProductID, SOD.ProductID
FROM sales.SalesOrderDetail sod
JOIN sales.SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID
JOIN [Production].[ProductCostHistory] pch on pch.ProductID = sod.ProductID
AND soh.OrderDate BETWEEN pch.startdate AND pch.EndDate

Any guidance would be appreciated.  Thanks 

[AlexisOlson · Accepted solution]

There are a variety of ways to traverse relationships but if you want to avoid worrying about stuff like context transitions and expanded tables, then you can go with a straightforward filter approach that doesn't use the relationships.

Not super efficient but it should work:

UnitCost =
VAR ProductID = SalesOrderDetail[ProductID]
VAR OrderDate = SalesOrderDetail[OrderDate]
RETURN
    MAXX (
        FILTER (
            ProductCostHistory,
            ProductCostHistory[ProductID] = ProductID
                && ProductCostHistory[StartDate] <= OrderDate
                && ProductCostHistory[EndDate] >= OrderDate
        ),
        ProductCostHistory[StandardCost]
    )

[AlexisOlson]

Pedantic side note: The sale order date isn't really the best match for looking up a historical price. Ideally, you'd use the purchase date but that requires more detailed inventory tracking than this model appears to support. Assuming inventory turnover is relatively quick, it's not a terrible approximation.

[smpa01]

@jjolk  Is it kindly possible to post the pbix please?

[AlexisOlson]

Can you show your relationship diagram?

When I download the AdventureWorks .pbix file it looks like this:

[jjolk]

Yes, unfortunately there are multiple versions of the Adventureworks database.  Here's the data model for the one I'm using.  Let me know if the screenshot below works.   I know I'm ignoring the Products table, but since I have ProductID in the SalesOrderDetail and ProductCostHistory table, at least in SQL Server, I'm okay. 

[AlexisOlson · Accepted solution]

There are a variety of ways to traverse relationships but if you want to avoid worrying about stuff like context transitions and expanded tables, then you can go with a straightforward filter approach that doesn't use the relationships.

Not super efficient but it should work:

UnitCost =
VAR ProductID = SalesOrderDetail[ProductID]
VAR OrderDate = SalesOrderDetail[OrderDate]
RETURN
    MAXX (
        FILTER (
            ProductCostHistory,
            ProductCostHistory[ProductID] = ProductID
                && ProductCostHistory[StartDate] <= OrderDate
                && ProductCostHistory[EndDate] >= OrderDate
        ),
        ProductCostHistory[StandardCost]
    )

[jjolk]

Well, I was too quick to accept the solution.  Reading it through, it seemed perfect.  However, Power BI has other thoughts on the matter.  I get the following error message.

Not sure why it doesn't like the statement since it is using MAXX.  

[AlexisOlson]

Are you trying to define this as a measure? Your original post asked for a calculated column.

[jjolk]

You are correct, I did actually try this first as a measure even though I meant to create a column.  I went back and tried it as a column and received a similar message.  Note, I made a couple of change to the script.  I referenced SalesOderHeader for [OrderDate] and I changed the date comparisons so [OrderDate] is between the start and end dates.  

[AlexisOlson · Accepted solution]

Since OrderDate is coming from a different table, try this:

VAR OrderDate = RELATED ( SalesOrderHeader[OrderDate] )

I think I already had the date inequalities correct. Assuming StartDate <= EndDate, you have

OrderDate <= StartDate <= EndDate <= OrderDate

 which can only be true if these are all equal.

[jjolk]

Thank you. Added the Related function fixed it.  This gives me a solution that I can now explore and try to make sure I understand it better. I appreciate your help.  

Julie