Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Currency conversion based on type and date

Another day, another PBI case 🙂 I'm completely turned around with this one. What I'd like to do is determine a transaction value for a given conversion rate based on a given timeframe. I have two ta...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    You can add the below as a calculated column

    USD Value =
VAR convRate =
    MAXX (
        TOPN (
            1,
            FILTER (
                'Currency Conversion',
                'Currency Conversion'[Currency Type] = 'Transactions Table'[Currency]
                    && 'Currency Conversion'[Date time] <= 'Transactions Table'[Date time]
            ),
            'Currency Conversion'[Date time]
        ),
        'Currency Conversion'[Conversion]
    )
RETURN
    'Transactions Table'[Value] * convRate
Anonymous's avatar
Anonymous
Not applicable
4 years ago

johnt75 this is great. i need to look into TOPN, but this works.

 

out of curiosity and keeping in mind the size of the database, would you say that it's better practice to house the dates in the column headers and conversion populated horizontally in rows? I'm tempted to go this route, but the dates as column headers makes things a bit messy.

johnt75's avatar
johnt75
Super User
4 years ago

I definitely wouldn't do that. A row per date per currency is the way to go.