Forum Discussion

Rajiv1237's avatar
Rajiv1237
Icon for Resolver I rankResolver I
8 years ago
Solved

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 curren...
Architecture
power bi
  • Rajiv1237's avatar
    Rajiv1237
    8 years ago

    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/

     

     

     

     

     

     

    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]))
)

     

     

     

     

Rajiv1237's avatar
Rajiv1237
Icon for Resolver I rankResolver I
8 years ago

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/

 

 

 

 

 

 

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]))
)