Currency Conversions & Incremental Refreshes of Power BI Datasets: Advice sought!

[leesm1thy]

Hi

I have a requirement to ensure that monetary amounts across multiple currencies can be expressed in a single user-selected currency. I've done this many times with Datasets - usually either in SQL or in Power Query, I will simply convert every amount in to a single currency (I pick EUR), and then have DAX measures that react to a user selection and, using the same exchange rate table, convert back from that. 

But I now have a need to use the Incremental Refresh, because of the size and length of time the dataset will cover. 

What's the best, most efficient method to ensure my users can have the same functionality as they're used to? Where should the exchange rate conversion happen? Note, that the exchange rates change every day - we do not want the strength of one currency against another to be a factor. This means that even rows of data from years ago need to have a new exchange rate applied to them at each daily refresh - does this mean I simply can't use Incremental Refresh and the benefits that brings?

Is there something I can do on the data model/DAX side to be able to achieve this?  

[leesm1thy · Accepted solution]

This is one of those times where you review the way you've been doing something for ages and realise there was a much, much better, simpler, easier way all along and now I'm kicking myself a bit...  I don't need columns that flatten the currency, I don't need inter-mediate measures that convert it back again. Looks like I need to revisit a lot of other Power BI Datasets!

Sum Monetary Value =

VAR _SumValueInEUR =

//What's the value in the column in a single currency (EUR). Table 'Exchange Rates' converts all possible currencies to 1 EUR : 

    SUMX (

        'Property',

        'Property'[Original Monetary Value]

            * LOOKUPVALUE (

                'Exchange Rates'[Exchange Rate],

                'Exchange Rates'[Currency], 'Property'[Currency of Monetary Value]

            )

    )

// What currency has the user selected:

VAR _UserSelectedCurrency =

    SELECTEDVALUE ( 'Exchange Rates'[Currency], "EUR" ) // EUR if no selection made

// What exchange rate does that currency have

VAR _UserSelectedExchRate =

    LOOKUPVALUE (

        'Exchange Rates'[Exchange Rate],

        'Exchange Rates'[Currency], _UserSelectedCurrency

    )

// What's the conversion to the user's selected currency

VAR Result =

    DIVIDE ( _SumValueInEUR, _UserSelectedExchRate, 0 )

RETURN

    Result