Running Total multiple currency conversion to one

nauriso1

Sure.

To put the Running Total EUR measure in words:

For each currency, calculate the Running Total in that currency, then multiply that by the Exchange Rate as at the date selected (or last date if multiple dates are selected).

Or describing what the DAX is doing:

1. SUMX iterates over each currency
2. For each currency, the [Running Total] measure returns the Running Total at the currently selected date (or max date)
3. This is multiplied by VALUES ( dimCurrencyExchange[Exchange Rate] ), calculated in the context of LASTDATE ( dimCalendar[Date Key] ) which should just have one value.
4. Since the VALUES(...) from Step 3 is wrapped in a CALCULATE, context transition means the current Currency is added to the filter context, and filters the dimCurrencyExchange table appropriately.

One potential performance issue is that we are iterating over all currencies, regardless of whether they have had transactions so far. If it is likely that they all have, then this doesn't matter.

Otherwise, you may consider iterating over just those currencies that have occurred so far, but I would test performance to see if it is worth it. Something like this:

Running Total EUR =
SUMX (
    CALCULATETABLE (
        CALCULATETABLE ( dimCurrency, fctTable ),
        DATESBETWEEN ( dimCalendar[Date Key], BLANK (), MAX ( dimCalendar[Date Key] ) )
    ),
    [Running Total]
        * CALCULATE (
            VALUES ( dimCurrencyExchange[Exchange Rate] ),
            LASTDATE ( dimCalendar[Date Key] )
        )
)

Cheers,

Owen