cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate - Apply filter based on a IF condition

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Calculate - Apply filter based on a IF condition

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] )
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Frequent Visitor

## Re: Calculate - Apply filter based on a IF condition

Community Support Team

## Re: Calculate - Apply filter based on a IF condition

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] )
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements