Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rajiv1237
Resolver I
Resolver I

Dynamic Exchange Rate architecture and DAX calculation in Power BI

I want to get my amount to change as per currency code selected dynamically.

 

In my FactSales table I have currency and Amount. If user want to see that amount in any currency he select the currency from slicer and my excahnge rate will apply to that amount.

 

I tried below DAX to get dynamic Exchange Rate but no luck:

 

 

ExchangeRate = CALCULATE(MIN(ExchangeRate[ExcahngeRate]), FILTER(ExchangeRate, AND(AND(AND(MIN(FactSales[TransDate])>=MIN(ExchangeRate[ValidFrom]), MIN(FactSales[TransDate])<=IF(ISBLANK(MIN(ExchangeRate[ValidTo])), DATEVALUE("01-01-2070"), MIN(ExchangeRate[ValidTo]))), MIN(ExchangeRate[FromCurrencyCode]) = MIN(FactSales[CurrencyCode])), MIN(ExchangeRate[ToCurrencyCode]) = FIRSTNONBLANK(ExchangeRate[ToCurrencyCode],1))))

 

ExchangeRate = CALCULATE(MIN(ExchangeRate[ExcahngeRate]), FILTER(ExchangeRate, AND(AND(AND(MIN(FactSales[TransDate])>=MIN(ExchangeRate[ValidFrom]), MIN(FactSales[TransDate])<=IF(ISBLANK(MIN(ExchangeRate[ValidTo])), DATEVALUE("01-01-2070"), MIN(ExchangeRate[ValidTo]))), MIN(ExchangeRate[FromCurrencyCode]) = MIN(FactSales[CurrencyCode])), MIN(ExchangeRate[ToCurrencyCode]) = FIRSTNONBLANK(ExchangeRate[ToCurrencyCode],1))))

 

https://drive.google.com/file/d/1BCD3cycq-tNTc9xPEK458dn-BjJj9Qao/view?usp=sharing

 

DynamicExchangeRate_1.PNGDynamicExchangeRate_2.PNG 

1 ACCEPTED SOLUTION
Rajiv1237
Resolver I
Resolver I

If we have Exchange rate on daily basis then we can go for below solution:

https://www.kasperonbi.com/currency-conversion-in-dax-for-power-bi-and-ssas/DynamicExchangeRate_3.PNG

 

 

 

 

 

 

If we have Exchange Rate as date range then use dynamic DAX:

ExchangeRate = 
IF(ISBLANK
	(
		CALCULATE(MIN(ExchangeRate[ExcahngeRate]), 
		FILTER(FILTER(ExchangeRate, AND(AND(ExchangeRate[FromCurrencyCode] = MIN(FactSales[TransactionCurrencyCode]), ExchangeRate[ToCurrencyCode] = FIRSTNONBLANK(ExchangeRate[ToCurrencyCode],1))
		, MIN(FactSales[TransDate])<=IF(ISBLANK(ExchangeRate[ValidTo].[Date]), DATEVALUE("2070-01-01"), ExchangeRate[ValidTo]))), MIN(FactSales[TransDate])>= ExchangeRate[ValidFrom]))
	)
	, 1
	, CALCULATE(MIN(ExchangeRate[ExcahngeRate]), 
		FILTER(FILTER(ExchangeRate, AND(AND(ExchangeRate[FromCurrencyCode] = MIN(FactSales[TransactionCurrencyCode])
		, ExchangeRate[ToCurrencyCode] = FIRSTNONBLANK(ExchangeRate[ToCurrencyCode],1))
		, MIN(FactSales[TransDate])<=IF(ISBLANK(ExchangeRate[ValidTo].[Date]), DATEVALUE("2070-01-01"), ExchangeRate[ValidTo])))
		, MIN(FactSales[TransDate])>= ExchangeRate[ValidFrom]))
)

DynamicExchangeRate_1.PNG

 

DynamicExchangeRate_2.PNG

 

 

 

View solution in original post

1 REPLY 1
Rajiv1237
Resolver I
Resolver I

If we have Exchange rate on daily basis then we can go for below solution:

https://www.kasperonbi.com/currency-conversion-in-dax-for-power-bi-and-ssas/DynamicExchangeRate_3.PNG

 

 

 

 

 

 

If we have Exchange Rate as date range then use dynamic DAX:

ExchangeRate = 
IF(ISBLANK
	(
		CALCULATE(MIN(ExchangeRate[ExcahngeRate]), 
		FILTER(FILTER(ExchangeRate, AND(AND(ExchangeRate[FromCurrencyCode] = MIN(FactSales[TransactionCurrencyCode]), ExchangeRate[ToCurrencyCode] = FIRSTNONBLANK(ExchangeRate[ToCurrencyCode],1))
		, MIN(FactSales[TransDate])<=IF(ISBLANK(ExchangeRate[ValidTo].[Date]), DATEVALUE("2070-01-01"), ExchangeRate[ValidTo]))), MIN(FactSales[TransDate])>= ExchangeRate[ValidFrom]))
	)
	, 1
	, CALCULATE(MIN(ExchangeRate[ExcahngeRate]), 
		FILTER(FILTER(ExchangeRate, AND(AND(ExchangeRate[FromCurrencyCode] = MIN(FactSales[TransactionCurrencyCode])
		, ExchangeRate[ToCurrencyCode] = FIRSTNONBLANK(ExchangeRate[ToCurrencyCode],1))
		, MIN(FactSales[TransDate])<=IF(ISBLANK(ExchangeRate[ValidTo].[Date]), DATEVALUE("2070-01-01"), ExchangeRate[ValidTo])))
		, MIN(FactSales[TransDate])>= ExchangeRate[ValidFrom]))
)

DynamicExchangeRate_1.PNG

 

DynamicExchangeRate_2.PNG

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.