Forum Discussion
Running Total multiple currency conversion to one
Apologies, missed that detail.
So the running total of transactions in each local currency is revalued daily in EUR at the current rate.
This measure will do the trick in the model you uploaded:
Running Total EUR = SUMX ( dimCurrency, [Running Total] * CALCULATE ( VALUES ( dimCurrencyExchange[Exchange Rate] ), LASTDATE ( dimCalendar[Date Key] ) ) )The LASTDATE(...) is just there to ensure correct calculation at a total level when multiple dates are selected.
Model uploaded with new measure here for reference
Cheers,
Owen
Apologies, missed that detail.
So the running total of transactions in each local currency is revalued daily in EUR at the current rate.
This measure will do the trick in the model you uploaded:
Running Total EUR =
SUMX (
dimCurrency,
[Running Total]
* CALCULATE (
VALUES ( dimCurrencyExchange[Exchange Rate] ),
LASTDATE ( dimCalendar[Date Key] )
)
)The LASTDATE(...) is just there to ensure correct calculation at a total level when multiple dates are selected.
Model uploaded with new measure here for reference
Cheers,
Owen
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:
- SUMX iterates over each currency
- For each currency, the [Running Total] measure returns the Running Total at the currently selected date (or max date)
- This is multiplied by VALUES ( dimCurrencyExchange[Exchange Rate] ), calculated in the context of LASTDATE ( dimCalendar[Date Key] ) which should just have one value.
- 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
I was truly amazed with the elegant solution and trying to use it to solve my challenge. I've recreated the original solution in Excel and it works fine. However modifying it for my own challenge I can't make it work. I have to apply 2 different FX rates for the same month - one (average) for P&L accounts and another (closing) to the Balance sheet ones (therefore my currencies expanded to 4 letters - USDF. USDB etc, where F stands for flow, B is for Balance). I still have just one combination for currency and date in the currency exchange table, but my Running Total in CAD returns blanks. Can you please have a look at my attempt below?
Cheers,
Vlad.
Hi Vladisam
Apologies for taking a while to get back to you.
The structure of your data model seems fine as far as relationships and having the B/F variants of each currency.
Regarding the blank Running Total CAD measure:
When I created a simple PivotTable with your model it looked like this
The Running Total CAD measure does return values as long as there is a value defined in dimCurrencyExchange for the relevant date. However, your CADB values end at 8/1/2018, so you get blank running totals after that date. (When you view the measure value in the PowerPivot window, it displays the measure in an unfiltered date context).
You may also want to consider wrapping running total measures in an IF to check whether the date has gone past the last transaction date, and if so blank out the measure. Example on DAX Patterns here
Cheers,
Owen
