Forum Discussion

Mishutka's avatar
Mishutka
Frequent Visitor
5 years ago

Aggregating currency conversions

I am scratching my head trying to understand why this is not working. I have 3 tables. Movements (which contain transaction amounts by date and by currency columns [Account], [Transaction Currency...
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Variables in DAX are constants, however odd this appears to be. Yes, once you define a variable, its value will never change. This is why your totals are incorrect.

 

You need to calculate the correct x-rate for each individual account that is visible in the current context.

Mishutka's avatar
Mishutka
Frequent Visitor
5 years ago

OK, I now have a table that gives the correct results, but I am trying to solve the data lineage, thus:

SUMX(

SUMMARIZE (
        FILTER ( Movements, Movements[Date] < SDate ),
        Movements[Account],
        "AmountGBP",
            ROUND (
                CALCULATE (
                    SUMX (
                        Movements,
                        Movements[Amount.Base]
                            * LOOKUPVALUE (
                                'Exchange Rates'[RatetoGBP],
                                'Calendar'[Date], SDate,
                                'Exchange Rates'[Currency.Symbol], MAX ( Movements[Transaction Currency] )
                            )
                    ),
                    Movements[Transaction Status] = "Actual"
                ),
                2
            )
    ),

[AmountGBP])

 

but when put in a table with Accounts as rows, the result is nothing. 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

    [OpeningBalanceYr.GBP] =
VAR StartOfYearDate = STARTOFYEAR ( 'Calendar'[Date] )
var OpeningBalance = 
    SUMX(
        VALUES( Movements[Account] ),
        VAR AccountCurrency =
            CALCULATE(
                // This will grab the currency
                // that's used in the transactions
                // for the account currently in context
                // since all transactions on an account
                // have the same currency (that's an
                // assumption that you claim is true).
                MAX ( Movements[Transaction Currency] )
            )
        var XRate =
            LOOKUPVALUE (
                'Exchange Rates'[RatetoGBP],
                'Exchange Rates'[Date], StartOfYearDate,
                'Exchange Rates'[Currency.Symbol], AccountCurrency
            )
        var Result =
            XRate * CALCULATE (
                SUM ( Movements[Amount.Base] ),
                'Calendar'[Date] <= StartOfYearDate,
                // This last line is not needed if
                // 'Calendar' has been marked as a 
                // date table in the model.
                REMOVEFILTERS( 'Calendar' )
            )
        return
            Result
    )
return
    OpeningBalance

    By the way, you should never use SUMMARIZE to calculate anything in it. SUMMARIZE is a function that must only ever be used for grouping and nothing more. This function is seriously flawed and will never be fixed by Microsoft since that would introduce breaking changes. If you need to, please use the combination of ADDCOLUMNS/SUMMARIZE. But your latest formula above, even if it worked, would be very slow since you're trying to calculate more than is required. Simply put, you've got a huge number of redundant calculations in there.