cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmgnln
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?

 

DAX:

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
319/17/20201.3205
129/17/20201.29753
139/17/20200.757289
149/17/20201.17994
169/17/20200.013588

 

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 

 

 

Thanks!!

 

2 REPLIES 2
v-diye-msft
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
quickly.
lbendlin
Super User
Super User

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.