LookUp with Multiple Criteria in PowerPivot

[ravikiran]

Hi Gurus,

I have a query to solve in Power Pivot. Seems like a small formula in Excel, I am unable to crack it on Power Pivot.

I have 2 tables.
Table1: Name, DateIn, DateOut, Product
Table2: Name, DateAsOn

There are multiple values for the User to use different Product, but falls in different DateRange (DateIn - DateOut) (the user can change the product only after expiry of the previous product).

I attached the sample workbook with Tables linked to data in Excel worksheet. The supposed to be Answer is highlighted in the Excel File.

I need a DAX formula which can lookup in the Table1 and fetch me the Value specific to an ID on that Particular Date.

Something like this criteria:
1. Table1.ID = Table2.ID
2. Table1.DateIn <= Table2.DateAsOn
3. Table1.DateOut >= Table2.DateAsOn

Please help me on this trick.

Thanks in advance,
Ravi.

 

[powerdax.com]

Utilizing your sample workbook, do the following:

1. Add an extra column Table3[ProductID] that is the digit from your product.
2. Add a relationship on Name between the two tables.
3. Add a calculated field =CALCULATE( MAXA(Table3[ProductID]), FILTER( Table3, Table3[Date] <= MIN(Table4[Date]) && Table3[Date2] >= MIN(Table4[Date]) ) )

 

[KRISHNA__8]

still getting error post applying the formula

 

[Nebu]

Hi:

Using Power pivot & DAX.

=CALCULATE(VALUES(Table3[Product]),FILTER(Table3,Table4[Name]=Table3[Name] && Table4[Date]>=Table3[Date] && Table4[Date]<=Table3[Date2]))

Thanks