cancel
Showing results for
Did you mean:
Highlighted Helper I

## Convert one currency into another with monthly exchange rates

Want to make one column where it converted AUD & GBP currency to USD. One more dataset is given - "Exchange rate" where every month average data exchange rate is given.

I want to write one DAX column where I need to convert the Amount in AUD & GBP to USD.

I am facing problem in writing DAX column query. I am new to Power BI.

If Organisation =1 then it is AU

If Organisation =2 then it is GBP

If Organisation =3 then it is US.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Super User IV

## Re: Convert one currency into another with monthly exchange rates

Try this calculated column.

``````Amount(USD) =
VAR __Organization='OLAP CUBE_Finance_WB'[Organisation]
VAR __DateString=FORMAT('OLAP CUBE_Finance_WB'[Date],"")
VAR __Year=MID(__DateString,1,4)
VAR __Month=MID(__DateString,5,2)
VAR __Day=MID(__DateString,7,2)
VAR __Date=DATE(__Year,__Month,__Day)
VAR __Amount='OLAP CUBE_Finance_WB'[Amount]
VAR __GBPtoUSD=LOOKUPVALUE('Exchange Rate'[Rate to GBP/USD],'Exchange Rate'[Date(GMT)],__Date)
VAR __AUDtoUSD=LOOKUPVALUE('Exchange Rate'[Rate to AUD/USD],'Exchange Rate'[Date(GMT)],__Date)
VAR __result =
SWITCH (
__Organization,
1, __AUDtoUSD * __Amount,
2, __GBPtoUSD * __Amount,
3, __Amount
)
return __result``````

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Appreciate with a kudos 👍

Proud to be a Super User!

Highlighted Super User IV

## Re: Convert one currency into another with monthly exchange rates

Try this calculated column.

``````Amount(USD) =
VAR __Organization='OLAP CUBE_Finance_WB'[Organisation]
VAR __DateString=FORMAT('OLAP CUBE_Finance_WB'[Date],"")
VAR __Year=MID(__DateString,1,4)
VAR __Month=MID(__DateString,5,2)
VAR __Day=MID(__DateString,7,2)
VAR __Date=DATE(__Year,__Month,__Day)
VAR __Amount='OLAP CUBE_Finance_WB'[Amount]
VAR __GBPtoUSD=LOOKUPVALUE('Exchange Rate'[Rate to GBP/USD],'Exchange Rate'[Date(GMT)],__Date)
VAR __AUDtoUSD=LOOKUPVALUE('Exchange Rate'[Rate to AUD/USD],'Exchange Rate'[Date(GMT)],__Date)
VAR __result =
SWITCH (
__Organization,
1, __AUDtoUSD * __Amount,
2, __GBPtoUSD * __Amount,
3, __Amount
)
return __result``````

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Appreciate with a kudos 👍

Proud to be a Super User!  