Calculate AVG Cost Per Unit BY QTR

[pbeeyeqs]

What I am trying to do is to create an additional column in a matrix visualization that looks at a UNIT ID and gets the AVG COST of that UNIT for a specific QTR by looking in the PRICING table and getting the AVG COST during that period. (A unit may have a price change in that qtr, multiple price changes, or a previous qtr change). 

I have been going through different formulas and nothing gives me what I am expecting. 

Here are my tables and the current MATRIX. 

Any assistance would be appreciated on how to create a measure on the AVG COST PRICE. 

Thanks

I have 3 tables:

a) PRODUCT NAMES

b) SALES

c) COST CHANGES

I have pulled a MATRIX VISUALIZATION together that correctly shows QTY UNITS SOLD, TOTAL $ SOLD and a measure using SUMX showing AVG SALE PER UNIT by YEAR and QTR. Seems to work Perfectly. 

[MarkLaf · Accepted solution]

I think I've come up with a solution, but it involves leveraging some helper tables. I'd be interested if there is a more straightforward way to do it... Anyway:

Create (i.e. New Table button in Modeling ribbon) a CALENDAR table:

CALENDAR = CALENDARAUTO() 

Add a custom column:

QTRKEY = [Date].[Year]&[Date].[QuarterNo]

Create similar column in SALES:

QTRKEY = [YEAR]&[QTR]

Create a QTRBRIDGE table to do a many2many connection with SALES:

QTRBRIDGE = SUMMARIZE('CALENDAR','CALENDAR'[QTRKEY])

Add relationships:

Create a measure that calculates the average product price over CALENDAR based off COST CHANGES:

AVGCOSTcal = 
AVERAGEX('CALENDAR',
    CALCULATE(AVERAGE('COST CHANGES'[UNIT COST]),
        FILTER('COST CHANGES',
            'COST CHANGES'[PRICE CHANGE] = 
            CALCULATE(
                MAX('COST CHANGES'[PRICE CHANGE]),
                FILTER(
                    ALLEXCEPT('COST CHANGES','COST CHANGES'[UNIT ID]),
                    'CALENDAR'[Date]>='COST CHANGES'[PRICE CHANGE]
                )
            )
        )
    )
)

Create a measure that you can throw on the SALES MATRIX to show the average price of your products by QTR:

AVGCOST = CALCULATE([AVGCOSTcal],'QTRBRIDGE','SALES')