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've got a sales tables with transactions spanning every weekday. The sales transaction is logged in local currency (EUR) but I want to convert the report to USD. My currencyrate table is however only updated on saturdays. So I would like get the latest available currencyrate to calculate my report with. Any good ideas?
For the example below I would like to get 1,18 and 1,2 for the two different transactions and sum up the total
Sales:
Date | Salesamount | Currency
2017-08-28 | 50 | EUR
2017-09-04 | 100 | EUR
Currencyrates
Date | Currency | Rate
2017-08-26 | EUR/USD | 1,18
2017-09-02 | EUR/USD | 1,2
Solved! Go to Solution.
Hi @Markando,
Based on my test, you should be able to use the formula below to add a calculate column in Sales table to get the latest available currencyrate, and then use it in other calculations.
Rate = CALCULATE ( MAX ( Currencyrates[Rate] ), FILTER ( Currencyrates, Currencyrates[Date] <= Sales[Date] ) )
SalesamountUSD = Sales[Salesamount]*Sales[Rate]
Regards
Hi @Markando,
Based on my test, you should be able to use the formula below to add a calculate column in Sales table to get the latest available currencyrate, and then use it in other calculations.
Rate = CALCULATE ( MAX ( Currencyrates[Rate] ), FILTER ( Currencyrates, Currencyrates[Date] <= Sales[Date] ) )
SalesamountUSD = Sales[Salesamount]*Sales[Rate]
Regards
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |