Currency Conversion Calculation: use most recent conversion rate to replace rate in future

[minmin99]

Hi everyone,

I am creating a measure to convert currency. The logic of currency conversion is that if the opportunity has a future close date, then use the most recent exchange rate.

But my measure doesn't show any conversion rate when there is a future close date.

ConversionRate = CALCULATE(IF(ISBLANK(CALCULATE(FIRSTNONBLANK(VALUES('Dated Conversion Rate'[ConversionRate]), 'Dated Conversion Rate'[ConversionRate]), FILTER('Dated Conversion Rate','Dated Conversion Rate'[StartDate] = FIRSTDATE('Opportunity'[CloseDate]) && 'Dated Conversion Rate'[IsoCode] = FIRSTNONBLANK('Opportunity' [CurrencyIsoCode], 'Opportunity' [CurrencyIsoCode])))), LOOKUPVALUE('Currency Type'[ConversionRate], 'Currency Type'[IsoCode], FIRSTNONBLANK('Opportunity' [CurrencyIsoCode], 'Opportunity' [CurrencyIsoCode])), CALCULATE(FIRSTNONBLANK(VALUES('Dated Conversion Rate'[ConversionRate]), 'Dated Conversion Rate'[ConversionRate]), FILTER('Dated Conversion Rate','Dated Conversion Rate'[StartDate] = FIRSTDATE('Opportunity'[CloseDate]) && 'Dated Conversion Rate'[IsoCode] = FIRSTNONBLANK('Opportunity' [CurrencyIsoCode], 'Opportunity'[CurrencyIsoCode])))))

CloseDate is located in the opportunity table; NNARR revenue is located in the opportunity product table and Startdate is the key to link opportunity table and date table.

The date models between table used:

Thanks!

[v-lionel-msft]

Hi  @minmin99 ,

Your formula is too poorly readable,  so I suggest you split it into multiple measures and check for problems.

Best regards,
Lionel Chen

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

[Greg_Deckler]

https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814

In your case, you are looking up the MAX of the data with appropriate filters.