Get value from another table using intermediary table

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]
    )

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.