Help with converting source cashflows in consistent currency

[dxg169]

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

[johnt75 · Accepted solution]

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

[dxg169]

Thank 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.

[johnt75]

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.