Currency Exchange rate today only

[pswprimera]

Hello,

I have what seems to be a tricky task using DAX in Power BI.

I have 3 columns

Date

Amount

exchange rate

I need todays exchange rate only to calculate historical revenue. I dont want to use tomorrows exchange rate to calculate tomorrows revenue.

Amount / exchange rate (today) = Revenue

The 4th Column or Measure Revenue must only use todays exchange rate.

Please help I've tried everything and it seems over complicated.

[pswprimera]

Still not a work around for the date. The values always revert back to the originasl date or are accuracy is not correct. Are there any other more simpler solutions for what is a potentially simple calculation?

[v-shex-msft]

Hi @pswprimera,

Since I still not clear for your table structure and relationships, can you share me a pbix file with sample data, I will modify the formula based on it.

Regards,

Xiaoxin Sheng

[pswprimera]

There are two tables with no relationships. There is a power bi left outer join to bring in exchange rate. Do you need the pbix file for this?

[v-shex-msft]

Hi @pswprimera,

>>var temp=MAXX(FILTER(ALL('price'),'price'[Date]=EARLIER('asgardprod pnr_price'[Date])&&'price'[EXRATE]=EARLIER('price'[EXRATE])),'price'[EXRATE])

You said these table not contain the relationship, why you use the earlier function on an unrelated column?

>>Do you need the pbix file for this?

Can you please upload it and share us a 1dr link?

Regards,

Xiaoxin Sheng

[pswprimera]

Here is the PBIX file.

https://1drv.ms/u/s!AgYN4lvosxGXhH-g8ClyTuBIc5S-

[v-shex-msft]

Hi @pswprimera,

You can refer to below attachment file.

BTW, the conditions of lookupvalue are use "and" to link, if you add multiple conditions, it will try to find out the records which match all of the conditions.

Regards,

Xiaoxin Sheng

[pswprimera · Accepted solution]

Thank you for working this one out. Much appreciated. The following is a very good solution:

Ex Rate = MAXX(FILTER(ALL('price'),'price'[creation_time]=TODAY() &&'price'[currency]=EARLIER('price'[currency])),'price'[EX Rate])

followed by for if you have multiple currencies in your data when calculating revenue:

Current / Ex Rate =
var temp= MAXX(FILTER(ALL('price'),'price'[creation_time]=TODAY() &&'price'[currency]=EARLIER('price'[currency])),'price'[EX Rate])
return
IF('price'[strRefKey]= "DKK"||
'price'[strRefKey]= "NOK"||
'price'[strRefKey]= "SEK"||
'price'[strRefKey]= "ISK",'price'[Amount] / temp ,
BLANK()
)