Lookup value with multiple filters

[JimJim]

Hi Team,
I have two tables, Sales and Currency. See below:

I would like to populate Sales.Exchange Rate GBP and Sales.Exchange Rate USD with the exchange rates from the currency table (values above are my expected results)
The logic I need to use looks something like this (For GBP):

select earliest exchange rate from Currency where Sales.Currency = Currency.Source and Currency.Target = 'GBP' and Sales.Date > Currency.Date

How can I accomplish this?

[JimJim]

Thank you @Jihwan_Kim ,

I appreciate the time you spent doing this, I will try and make it work

[Jihwan_Kim]

Qty total : =

SUM(Sales[Qty])

Currency : =

IF( ISFILTERED(Dates[Date]),

SELECTEDVALUE(Sales[Currency]))

Exchange rate GBP =

VAR _sourcecurrency =

MAX ( SourceCurrency[Source] )

VAR _currentdate =

MAX ( Dates[Date] )

RETURN

IF (

NOT ISBLANK ( [Qty total :] )&& ISFILTERED(Dates[Date]),

CALCULATE (

LASTNONBLANKVALUE (

Dates[Date],

CALCULATE (

SUM ( 'Currency'[Rate] ),

FILTER (

'Currency',

'Currency'[Source] = _sourcecurrency

&& 'Currency'[Target] = "GBP"

)

)

),

FILTER ( ALL ( Dates ), Dates[Date] <= _currentdate )

)

)

Exchange rate USD =

VAR _sourcecurrency =

MAX ( SourceCurrency[Source] )

VAR _currentdate =

MAX ( Dates[Date] )

RETURN

IF (

NOT ISBLANK ( [Qty total :] ) && ISFILTERED(Dates[Date]),

CALCULATE (

LASTNONBLANKVALUE (

Dates[Date],

CALCULATE (

SUM ( 'Currency'[Rate] ),

FILTER (

'Currency',

'Currency'[Source] = _sourcecurrency

&& 'Currency'[Target] = "USD"

)

)

),

FILTER ( ALL ( Dates ), Dates[Date] <= _currentdate )

)

)

https://www.dropbox.com/s/ugvjurqz5uno5r7/jimjim.pbix?dl=0 

[JimJim]

I see you have created a SourceCurrency table, is this a necessary step in addition to my current currency table? My source currencies have more than just AUD so ideally I would rather not have to create this

J

[Jihwan_Kim]

You are right.
If you do not have other currencies, it is not mandatorily needed.

[JimJim]

Hi @Jihwan_Kim 

I have tried to implement your solution but it is not working, I have attached a link to a PowerBI model. As you can see I am trying to add a new column to my table 'Requests' but it is returning no results

https://1drv.ms/u/s!Avci50weR29DvlH-xbTb5oK5NI7U?e=qyjClq 

[Jihwan_Kim · Accepted solution]

https://www.dropbox.com/s/o04lz90nbzttotv/reporting%20model.pbix?dl=0 

Exchange rate GBP CC=
VAR sourcecurrency = Requests[Currency]
VAR targetcurrency = "GBP"
VAR currentdate = Requests[Date Requested]
VAR maxcurrencydate =
CALCULATE (
MAX ( 'Currency'[date] ),
FILTER (
'Currency',
'Currency'[date] <= currentdate
&& 'Currency'[sourcecurrency] = sourcecurrency
&& 'Currency'[targetcurrency] = targetcurrency
)
)
RETURN
CALCULATE (
CALCULATE ( SUM ( 'Currency'[exchangerate] ) ),
FILTER (
'Currency',
'Currency'[date] = maxcurrencydate
&& 'Currency'[sourcecurrency] = sourcecurrency
&& 'Currency'[targetcurrency] = targetcurrency
)
)

[JimJim]

Hi @Jihwan_Kim ,

Thank you, it works.

I had to add a DATEVALUE function to my currentdate variable as it was complaining about comparing date and strings.