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
data Model

facttable contains , ItemId, Date, CurrencyID & amount

currencyExchange table contains Date, CurrencyId & exchange rate

dimCurrencyExchange

result what I whant get would be like this (as measure not calculated column, this only to understand calculation, that each currency running total is converted to EUR and then summed together)
result

currently I have created measure, which partly solves this problem (it is working while there is no in one date multiple transactions with different currencies) ([Total] is defined as SUM(fctTable[Amount])

Running Total All EUR:=SUMX(FILTER(ALL(dimCalendar);dimCalendar[DateKey]<=MAX(dimCalendar[DateKey]));[Total]*
LOOKUPVALUE(
dimCurrencyExchange[ExchangeRate];
dimCurrencyExchange[DateKey];CALCULATE(VALUES(dimCalendar[DateKey]));
dimCurrencyExchange[CurrencyId];CALCULATE(VALUES(fctTable[CurrencyId]))))

What could be the solution to get appropriate currency rate to given dates if there is no transactions, but running total would return value?
thanks in advance

 

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

8 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    9 years ago

    Hi nauriso1

     

    I would tweak the data model & measures slightly to get this to work:

    Sample model here

     

    First of all, I would suggest you add a dimCurrency table to your data model, which contains a unique list of currency IDs/Names, and relate dimFX and fctTable to dimCurrency.

    So you end up with fctTable & dimFX both related to dimCalendar & dimCurrency:

     

    Then I would define the measures below. [Amount EUR] iterates over the combinations of Date/Currency present in fctTable and uses relationships to look up the exchange rate (at Date/Currency granularity):

     

    Amount Local Currency = 
SUM ( fctTable[Amount] )

Amount EUR =
SUMX (
    SUMMARIZE ( fctTable, dimCalendar[DateKey], dimCurrency[CurrencyId] ),
    // Note: VALUES ( dimFX[ExchangeRate] ) is safe because
        // dimFX[ExchangeRate] has exactly one value for any DateKey/CurrencyId combination
        CALCULATE (
        VALUES ( dimFX[ExchangeRate] )
    )
        * [Amount Local Currency]
)

Amount EUR Cumulative = 
CALCULATE (
    [Amount EUR],
    DATESBETWEEN ( dimCalendar[DateKey], BLANK (), MAX ( dimCalendar[DateKey] ) )
)

    Then the output looks like this:

    Variations on this are possible, but this seems a good way of doing it to me.

     

    You could also pre-calculate [Amount EUR] in fctTable either in the Query Editor or with DAX.

     

    Hopefully that is of some use.
    Cheers,

    Owen :)

    • nauriso1's avatar
      nauriso1
      Frequent Visitor
      9 years ago

      Thanks OwenAuger for your replay, but I need, that running total is converted to EURos not transactions. In your case at first are transactions converted to EUR and then running total is made, which is not correct (because exchange rate used for running total is based on transaction date exchange rate, but I need that for running total would be applied appropriate date exchange rate regardless of transaction date.. So thats mean, tad running total should be converted by each date exchange rate) 
      I attached excel and pbx file. Hope that this will more clarify what I want achieve

      RunningTotalCurrency_Conversion.pbix

      RunningTotalCurrency_Conversion.xlsx

       

      • OwenAuger's avatar
        OwenAuger
        Super User
        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