Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Min Su]

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 tables used:

 

 Thanks!

 

 

 

 

 

 

 

 

Thanks!

[Ashley Hsia]

Hi Min Su,

It would help if you could provide a sample of the data table you are working with.  You mention future date – does this mean there is an actual date in the future Power BI can look at or is anything in the future simply blank? 
If you do have dates ?some of the logic in this post might be helpful:

https://community.powerbi.com/t5/Desktop/Calcuate-the-closest-less-than-date/td-p/21113

You may also want to try using the NOW() function to say if date is less than NOW(), use closest rate, etc.
Best of luck,

——————————
AHsia
——————————
——————————————-

[Min Su]

Hi Ashley,

There is an actual date in the future power bi can look up. 
Here is the opportunity table in which I would like to create a conversionRate calculated column.

Here is the dated conversion rate table that storages updated conversion rate information. In a single day, there are conversion rates for multiple currencies.  

Thanks,
Zimin

——————————
Min Su
Sales Operation Analyst
Washington DC
——————————
——————————————-