Calculate - Apply filter based on a IF condition

[bmbhelper]

Hi Guys,

I don't know how to explain or title this. Let me give you my best shot.

I have a set of sales data, I want to create a measure that takes Qty * sale price per sku. Now the Issue is that the sale price changes every period.

How can I make a measure where the filter is dynamic according to the period inwhich the sale happened.

Something like this

Sales:=Qty times (sale price filtered by the sale's period price).

Does that make sense?

Raw Data
Customer	Product	Period	Qty
John	Product A	Period 1	20
Joe	Product B	Period 2	1
Geo	Product A	Period 3	10
Mary	Product C	Period 4	5
Patrick	Product C	Period 5	8

Product Table
Product	Period 1	Period 2	Period 3
Product A	1.5	1.5	2
Product B	1.5	1.55	2
Product C	2	1.5	2

Thank you in advance!! 

[v-chuncz-msft · Accepted solution]

@bmbhelper,

Right click [Product] column from Product Table and select Unpivot Other Columns in Query Editor, then add a calculated column to Raw Data.

Price =
LOOKUPVALUE (
    'Product Table'[Value],
    'Product Table'[Product], 'Raw Data'[Product],
    'Product Table'[Attribute], 'Raw Data'[Period]
)

Measure =
SUMX ( 'Raw Data', 'Raw Data'[Qty] * 'Raw Data'[Price] )