Forum Discussion
Convert currency
This will work if you get rid of all of your non-GBP exchange rates. Do you need those? If you do, sure some more DAX magic can get you there.
Column = LOOKUPVALUE(ExchangeRates[ExchangeRate],ExchangeRates[CurrencyCode],"GBP",ExchangeRates[StartTime],MAX(ExchangeRates[StartTime]))
Oh wait, here is the additional DAX magic that will let you keep your other currency codes.
Column = LOOKUPVALUE(ExchangeRates[ExchangeRate],ExchangeRates[CurrencyCode],"GBP",ExchangeRates[StartTime],CALCULATE(MAX(ExchangeRates[StartTime]),ExchangeRates[CurrencyCode]="GBP"))
calerof - Can you provide example/sample source data and expected output?
Hello Greg_Deckler,
Sorry for not answering before, my client started requesting things afar from the agreement.
The data are as follows:
Fact Table with sales by invoice line:
https://www.dropbox.com/s/hz5nc6kqda4wbv9/Daily%20Revenue%20by%20invoice%20item%20Original.xlsx?dl=0
| Date | USD |
| 01-ene-19 | 19.6512 |
| 02-ene-19 | 19.6512 |
| 03-ene-19 | 19.5878 |
| 04-ene-19 | 19.6073 |
| 05-ene-19 | 19.6073 |
| 06-ene-19 | 19.6073 |
| 07-ene-19 | 19.4902 |
| 08-ene-19 | 19.3479 |
| 09-ene-19 | 19.3479 |
| 10-ene-19 | 19.2456 |
| 11-ene-19 | 19.1236 |
| 12-ene-19 | 19.1236 |
| 13-ene-19 | 19.1236 |
| 14-ene-19 | 19.1236 |
| 15-ene-19 | 19.0064 |
In excel I would do it just easily with =vlookup convert all sales in MXP to USD based on the invoice date and bring the sales already in USD to the same column to have a total in USD, e.g.
https://www.dropbox.com/s/jvwex5u8o0l7mo9/Daily%20Revenue%20by%20invoice%20item.xlsx?dl=0
In this way the last column would show Sales in USD converted at the invoice date, not at month end.
When I use LOOUPVALUE my problem is when assigning the current date in SEARCH VALUE doesn't recognize the row context and get the error: "A table of multiple values was supplied where a single value was expected".