cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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.

 

Attach pbix file:https://drive.google.com/file/d/1JO9XO-x5cSuuVISVZRr6T3G66l6mSY1a/view?usp=sharing

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Convert one currency into another with monthly exchange rates

@shantanu97 

 

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



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂




Regards,
Nandu Krishna

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 👍

Proud to be a Super User!

View solution in original post

1 REPLY 1
Highlighted
Super User IV
Super User IV

Re: Convert one currency into another with monthly exchange rates

@shantanu97 

 

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



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂




Regards,
Nandu Krishna

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 👍

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors