Forum Discussion

dxg169's avatar
dxg169
Frequent Visitor
3 years ago
Solved

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 ne...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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
johnt75's avatar
johnt75
Super User
3 years ago

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's avatar
dxg169
Frequent Visitor
3 years ago

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's avatar
    johnt75
    Super User
    3 years ago

    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.