Calculated Column for Exchange Rates

[fernandoC]

Hi! 

I'm trying to create a calculated column that tells me the Exchange Rate of a particular value based both on a date and a location (i.e IF the salary expectation is from X location & X date, divide the salary expectation with the exchange rate that is between the start date & end date of the X date). 

Exchange rates table:

These are the columns used from the Fact table: 

I'm using the following formula but I'm not getting the correct results:

GBP - Salary Expectation = DIVIDE('Main Jira Info Prod'[Salary Expectations],IF('Main Jira Info Prod'[RESOLUTIONDATE] >= RELATED('Exchange Rates'[Start Date]) && 'Main Jira Info Prod'[RESOLUTIONDATE] <= RELATED('Exchange Rates'[End Date]), RELATED('Exchange Rates'[Exchange Rate]),0))

Both the Exchange Rates & the Fact table have an active relationship. 

Any insights would be greatly appreciated. 

Best, 

[V-lianl-msft]

Hi @fernandoC ,

Create a calendar table and then create relationships with the other two tables.

Create a new column in the calendar table.

rate = CALCULATE(
MAX('Table'[Exchange Rate]),
FILTER('Table','Calendar'[Date]>='Table'[Start Date]&&'Calendar'[Date]<='Table'[End Date]))

Refer to the sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fernandoC]

Hi @V-lianl-msft ,

Thank you very much for your help on this. 

I think the logic is going the right way,  the only thing I'm missing is that currently it takes a rate based on a date but I need also that it takes the value from an specific currency as it will vary depending on the location. (i.e MOD will use UYU currency but ISD would use a different one), Im guessing nested IF's would do the work. 

Thanks again for all your help. 

Best,