Forum Discussion

nauriso1's avatar
nauriso1
Frequent Visitor
9 years ago
Solved

Running Total multiple currency conversion to one

Hello   I have such simplified data model - with various currencies. I have to covert all those currencies in one  - running total EUR for each date in dimCalendar table facttable contains ,...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    nauriso1

    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

Vladisam's avatar
Vladisam
Helper II
9 years ago

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?

 

Example

 

Cheers,

 

Vlad.

 

OwenAuger's avatar
OwenAuger
Super User
9 years ago

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