Forum Discussion
Currency Conversion Calculation: use most recent conversion rate to replace rate in future
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!
2 Replies
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.
Hi Anonymous ,
Your formula is too poorly readable, so I suggest you split it into multiple measures and check for problems.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
