Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

"If" statement with not exists

Hi all,


Here is my situation:

I'm trying to get the currency exchange rate for each day into the sales table.

The currency exchange table has the fields "date_effective", "from currency id", "to currency id" and "exchange rate". It has two inactive relationships with the sales table via "dates" and "from currency id" and I always want the conversion to USD (currency id = 1).

I was using the formula below to get the rate and it worked well except that the currency exchange table does not have one day listed (system issues). For example 9/1/2020 does not exist on that table and when I use the formula below the result for that day comes out as blank. I wanted to include a condition on my formula below which will use the currency exchange rate from the day before in that case. Can anyone please help?



Exchange rate in USD = CALCULATE ( FIRSTNONBLANK ( Currencies[EXCHANGE_RATE], 1 ), FILTER ( Currencies, Currencies[DATE_EFFECTIVE] = 'Sales '[trandate] && Currencies[from currency] = 'Sales Transactions'[currency id] && Currencies[to currency] = 1 ) )


Data sample:

Currency exchange rate table 

from currencyto currencyDATE_EFFECTIVEEXCHANGE_RATE


Sales transactions (Last column is my dax):

tranidtrandate Revenue CURRENCY_IDExchange rate in USD - DAX
id19/21/2020 $     5.0040.834676
id29/29/2020 $     2.0040.855198
id39/21/2020 $     3.0040.848565
id69/1/2020 $     6.004 
id79/21/2020 $     7.0040.834676
id89/29/2020 $     8.0040.855198
id99/1/2020 $     9.004 





Community Support
Community Support

Hi @mmgnln 


Not really enough information to go on, you might consider providing your dummy pbix that would be helpful for us to investigate it further. 

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.


Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
Super User
Super User

You can choose  IF(ISBLANK()) or COALESCE() to handle these situations.

Helpful resources

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors