Proper Modeling for Cost Table

Hi MostlySunny ,

It looks as though your Cost table is a Slowly-Changing Dimension (SCD) table i.e. item rows get duplicated based on changing relevant dates, such as 'active from <date>, active to <date>'.

If so, then I'd recommend handling this in DAX. I generally recommend against using calculated columns in DAX but this is the one exception I make - I haven't found a faster or simpler method yet.

--Assumptions--

[Date Approved] sits in your Quote Line table and is a proper date.

[Start Year Month] is a proper date.

[End Year month] is a proper date.

CostTable[Material] and QuoteLine[Material] contain relatable values.

--Process--

Remove the relationship between CostTable and QuoteLine.

Add a new DAX calculated column in your QuoteLine table as follows:

..materialCost = 
CALCULATE(
    VAR __Material = VALUES(QuoteLine[Material])
    VAR __AppDate = VALUES(QuoteLine[Date Approved])
    RETURN
    MAXX(
        FILTER(
            costTable,
            costTable[Material] = __Material
            && costTable[Start Year Month] <= __AppDate
            && costTable[End Year Month] >= __AppDate
        ),
        costTable[Cost (LC)]
    )
)

You should find this to be far more performant than Many:Many relationships and Power Query gymnastics.

Pete