Forum Discussion
Help with converting source cashflows in consistent currency
Hi All - I'm sturggling to work out how to convert source cashflows in local currency into USD so I can then aggregate. I was initially trying to create a measure using look up to bring in fx rate needed, before creating another measure to multiply these. Note that the source file (table1) does not have the local currency code in it hence why I have a mapping table (table 2).
I have:
a source table of cashflows in local fund currency (either USD or EUR);
a fund mapping table which shows what the fund local currency is. This also has a parent fund mapping that I'll use to aggregate certain funds once the cashflows are converted;
a FX table of conversion rates for USD and EUR at each date. I have unpivoted this (i.e. the source file has a single row per date and a column of USD and column for EUR.
Table 1: Cashflows
| Date | Fund | Cashflow |
| 1/1/2001 | Fund A-EUR | 100 |
| 1/1/2001 | Fund A-USD | 110 |
| 1/2/2001 | Fund B-EUR | 120 |
| etc |
Table 2: Fund mapping
| Fund | Parent Fund | Fund Currency |
| Fund A-EUR | Fund A | EUR |
| Fund A-USD | Fund A | USD |
| Fund B-EUR | Fund B | EUR |
| etc |
Table 3: FX rates (this has been unpivoted)
| Date | Currency to USD | Fx Rate |
| 1/1/2001 | USD | 1 |
| 1/1/2001 | EUR | 1.21 |
| 2/1/2001 | USD | 1 |
| 2/1/2001 | EUR | 1.23 |
| etc |
Create a calculated column on the cashflows table like
Cashflow USD = VAR CurrentCurrency = RELATED ( 'Fund mapping'[Currency] ) VAR CurrentDate = 'Cashflows'[Date] VAR Rate = LOOKUPVALUE ( 'FX rates'[FX rate], 'FX Rates'[Date], CurrentDate, 'Fx Rates'[Currency], CurrentCurrency ) RETURN 'Cashflows'[Cashflow] * Rate
3 Replies
Create a calculated column on the cashflows table like
Cashflow USD = VAR CurrentCurrency = RELATED ( 'Fund mapping'[Currency] ) VAR CurrentDate = 'Cashflows'[Date] VAR Rate = LOOKUPVALUE ( 'FX rates'[FX rate], 'FX Rates'[Date], CurrentDate, 'Fx Rates'[Currency], CurrentCurrency ) RETURN 'Cashflows'[Cashflow] * RateThank you, this has worked.
For my own understanding, is there any way to do this as a measure? Or is that not fit for this purpose.
I don't think that would suit in this situation. There are several advantages of doing it as a column. The calculation only happens once, during data refresh, and so the end user will not see any performance problems, and with a column you can then create other measures on top of that. Having to calculate it on the fly for every visual would not give good performance.
