Hi all, I am rather stuck on the following problem and wonder if anyone can advise?
I have a table loaded with values in a single column which are a mixture of currencies.
| Period | Line | Value | Currency |
| 201912 | PAY ICO-- | -5,437,330.00 | EUR |
| 202000 | PAY A ICO-- | -5,400,297.41 | EUR |
| 201812 | PAY ICO-- | -4,763,415.20 | EUR |
| 201900 | PAY A ICO-- | -4,595,511.31 | EUR |
| 201900 | PAY ICO-- | -4,562,300.84 | CAD |
| 201800 | LIA OTH-- | -4,506,109.05 | EUR |
| 201812 | INC OTH-INT- | -4,414,344.44 | EUR |
| 201800 | PAY A ICO-- | -4,225,874.50 | EUR |
| 201912 | WIP-EXT- | -4,060,620.32 | GBP |
| 202007 | REC A ICO-- | -4,041,224.00 | EUR |
| 201912 | INC OTH-INT- | -4,024,194.23 | EUR |
I also have a table of conversion rates
| Period | EUR | CAD | GBP | CZK | US |
| 202000 | 1 | 1.455604076 | 0.846202677 | 25.42423757 | 1.122700752 |
| 202001 | 1 | 1.465075181 | 0.839898964 | 25.21925042 | 1.108084542 |
| 202002 | 1 | 1.478878153 | 0.860161867 | 25.45347221 | 1.102707385 |
| 202003 | 1 | 1.556267183 | 0.885086397 | 27.35171173 | 1.102358727 |
| 202004 | 1 | 1.5206582 | 0.867365751 | 27.08086703 | 1.093243896 |
| 202005 | 1 | 1.529053548 | 0.899557905 | 26.92582537 | 1.110623282 |
| 202006 | 1 | 1.529841269 | 0.908327248 | 26.6434015 | 1.123464042 |
| 202007 | 1 | 1.581933778 | 0.900287439 | 26.2527609 | 1.18192734 |
| 202008 | 1 | 1.557661801 | 0.893449343 | 26.26782629 | 1.195324388 |
| 202009 | 1 | 1.557661801 | 0.893449343 | 26.26782629 | 1.195324388 |
| 202010 | 1 | 1.557661801 | 0.893449343 | 26.26782629 | 1.195324388 |
| 202011 | 1 | 1.557661801 | 0.893449343 | 26.26782629 | 1.195324388 |
| 202012 | 1 | 1.557661801 | 0.893449343 | 26.26782629 | 1.195324388 |
I've also unpivoted the table to create this (not sure which is best option)
| Period | Currency | Rate |
| 202000 | EUR | 1 |
| 202000 | CAD | 1.455604 |
| 202000 | GBP | 0.846203 |
| 202000 | CZK | 25.42424 |
| 202000 | USD | 1.122701 |
| 202001 | EUR | 1 |
| 202001 | CAD | 1.465075 |
| 202001 | GBP | 0.839899 |
| 202001 | CZK | 25.21925 |
| 202001 | USD | 1.108085 |
| 202002 | EUR | 1 |
| 202002 | CAD | 1.478878 |
| 202002 | GBP | 0.860162 |
| 202002 | CZK | 25.45347 |
| 202002 | USD | 1.102707 |
| 202003 | EUR | 1 |
| 202003 | CAD | 1.556267 |
| 202003 | GBP | 0.885086 |
| 202003 | CZK | 27.35171 |
| 202003 | USD | 1.102359 |
| 202004 | EUR | 1 |
| 202004 | CAD | 1.520658 |
| 202004 | GBP | 0.867366 |
| 202004 | CZK | 27.08087 |
| 202004 | USD | 1.093244 |
| 202005 | EUR | 1 |
| 202005 | CAD | 1.529054 |
| 202005 | GBP | 0.899558 |
| 202005 | CZK | 26.92583 |
| 202005 | USD | 1.110623 |
| 202006 | EUR | 1 |
| 202006 | CAD | 1.529841 |
I have created a quick measure to calculate the average rates.
I would like to create an additional column in the first table which lists the value of each line in EUR as per below:
| Period | Line | Value | Currency | CalculatedValue in € |
| 201912 | PAY ICO-- | -5,437,330.00 | EUR | -5,437,330.00 |
| 202000 | PAY A ICO-- | -5,400,297.41 | EUR | -5,400,297.41 |
| 201812 | PAY ICO-- | -4,763,415.20 | EUR | -4,763,415.20 |
| 201900 | PAY A ICO-- | -4,595,511.31 | EUR | -4,595,511.31 |
| 201900 | PAY ICO-- | -4,562,300.84 | CAD | -251,326.98 |
| 201800 | LIA OTH-- | -4,506,109.05 | EUR | -4,506,109.05 |
| 201812 | INC OTH-INT- | -4,414,344.44 | EUR | -4,414,344.44 |
| 201800 | PAY A ICO-- | -4,225,874.50 | EUR | -4,225,874.50 |
| 201912 | WIP-EXT- | -4,060,620.32 | GBP | -4,449,345.55 |
| 202007 | REC A ICO-- | -4,041,224.00 | EUR | -4,041,224.00 |
| 201912 | INC OTH-INT- | -4,024,194.23 | EUR | -4,024,194.23 |
I know I need to lookup the currecny column and apply the conversion, however, I am struggling to write the expression.
Can anyone please help with this?
Many thanks in advance!
Alex
