Lookup Value between 2 tables and 2 dates

[jgoode]

Hi, im lookng for some help if possible. Thanks in adv.

I have 2 tables that im tyring to lookup and filter between:

 - the first table is a list of the transactions, per product per date.

 - the second is a table of products and the applicable saving between a certain date range.

Im struggling to lookup the product number from the transactions on the product table, then check which product is applicable between the date ranges of the transaction and return the saving.

Thanks,

example - 

Table 1

Table 2

To return

[tamerj1 · Accepted solution]

Hi @jgoode 

new column in Transaction

Saving =
MAXX (
FILTER (
'Product',
'Product'[Product ID] = Transaction[Product]
&& 'Product'[Satrt Date] <= Transaction[Date]
&& 'Product'[End Date] >= Transaction[Date]
),
'Product'[Saving]
)

[tamerj1 · Accepted solution]

Hi @jgoode 

new column in Transaction

Saving =
MAXX (
FILTER (
'Product',
'Product'[Product ID] = Transaction[Product]
&& 'Product'[Satrt Date] <= Transaction[Date]
&& 'Product'[End Date] >= Transaction[Date]
),
'Product'[Saving]
)

[jgoode · Accepted solution]

Thanks, worked perfectly.

[JadhavVarsha_13]

Hello,

I Believe there are 4 different ways for this,

very first would be building relationships, if you have 2 tables.

second - you can use RELATED

third - by using, LOOKUPVALUE(Result_ColumnName, Search_ColumnName1, Search_Value1, ..., [Alternate_Result])

Where,

Result_ColumnName – column from which you want to fetch/lookup the values into the current table. The column should be an existing one and not an expression.

Search_ColumnName1 – A lookup column based on which we want to fetch the result. It can be present in the same table or a related table.

Search_Value1 – a value to search in Search_ColumnName1.

[Alternate_Result] – an alternate result that appears in the case of advanced filters applied. It is an optional argument and, by default, provides a BLANK value unless specified extensively.

Last would be merge,which can be utilized while looking up values from different tables in Power BI is the Merge Queries option. It will allow you to combine two tables together based on a common column and then fetch the details based on lookup values.

hope this helps !