Lookup With Multiple Criteria in PowerPivot

[rksample]

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: ID, DateIn, DateOut, Value
Table2: ID, DateAsOn

There are multiple values for the Same ID, but falls in different DateRange (DateIn - DateOut)

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.

Thank you,
Ravi.

 

[scottsen]

It's going to depend a bit on where you want to use this, if there are dupes, and if you want a measure vs calculated column, but... it will probably look something like...

=CALCULATE(MIN(Table1[Value]), FILTER(Table1, Table1.Id = MIN(Table2.ID) && Table1.DateIn <= MIN(Table2.DataAsOn) && Table1.DateOut >= MIN(Table2.DateAsOn))

 

[rksample]

Thanks Scottsen for formula. I shall try this first thing morning.

And to answer you, yes there were duplicates (ID-Value combinations). Though ID-Value-DateIn-DateOut combination will be unique So for any specific Date an ID as one specific value which is to be Fetched.

Thanks again, will keep you posted on the result.

Cheers,
Ravi.