Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 currency | to currency | DATE_EFFECTIVE | EXCHANGE_RATE |
3 | 1 | 9/17/2020 | 1.3205 |
1 | 2 | 9/17/2020 | 1.29753 |
1 | 3 | 9/17/2020 | 0.757289 |
1 | 4 | 9/17/2020 | 1.17994 |
1 | 6 | 9/17/2020 | 0.013588 |
Sales transactions (Last column is my dax):
tranid | trandate | Revenue | CURRENCY_ID | Exchange rate in USD - DAX |
id1 | 9/21/2020 | $ 5.00 | 4 | 0.834676 |
id2 | 9/29/2020 | $ 2.00 | 4 | 0.855198 |
id3 | 9/21/2020 | $ 3.00 | 4 | 0.848565 |
id6 | 9/1/2020 | $ 6.00 | 4 | |
id7 | 9/21/2020 | $ 7.00 | 4 | 0.834676 |
id8 | 9/29/2020 | $ 8.00 | 4 | 0.855198 |
id9 | 9/1/2020 | $ 9.00 | 4 |
Thanks!!
Hi @Anonymous
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.
You can choose IF(ISBLANK()) or COALESCE() to handle these situations.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |