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
🙂

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
🙂

Announcements

#### Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021