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.
Hello,
I have what seems to be a tricky task using DAX in Power BI.
I have 3 columns
Date
Amount
exchange rate
I need todays exchange rate only to calculate historical revenue. I dont want to use tomorrows exchange rate to calculate tomorrows revenue.
Amount / exchange rate (today) = Revenue
The 4th Column or Measure Revenue must only use todays exchange rate.
Please help I've tried everything and it seems over complicated.
Solved! Go to Solution.
Thank you for working this one out. Much appreciated. The following is a very good solution:
Ex Rate = MAXX(FILTER(ALL('price'),'price'[creation_time]=TODAY() &&'price'[currency]=EARLIER('price'[currency])),'price'[EX Rate])
followed by for if you have multiple currencies in your data when calculating revenue:
Current / Ex Rate =
var temp= MAXX(FILTER(ALL('price'),'price'[creation_time]=TODAY() &&'price'[currency]=EARLIER('price'[currency])),'price'[EX Rate])
return
IF('price'[strRefKey]= "DKK"||
'price'[strRefKey]= "NOK"||
'price'[strRefKey]= "SEK"||
'price'[strRefKey]= "ISK",'price'[Amount] / temp ,
BLANK()
)
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 |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |