Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 🙂
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
104 | |
77 | |
71 | |
50 |
User | Count |
---|---|
145 | |
108 | |
107 | |
90 | |
65 |