Aggregating currency conversions

[Mishutka]

I am scratching my head trying to understand why this is not working.

I have 3 tables.

1. Movements (which contain transaction amounts by date and by currency columns [Account], [Transaction Currency], [Amount.Base]
2. Calendar (for dates - date column is [Date]
3. Exchange Rates - columns [Date], [Currency Symbol], [RatetoGBP] - currency conversion rate.

Each account has transactions in only one currency.

I have created a measure for the opening balance in GBP:

MEASURE Movements[OpeningBalanceYr.GBP] =
        VAR SDate =
            STARTOFYEAR ( 'Calendar'[Date] )
        VAR CurrS =
            MAX ( Movements[Transaction Currency] )
        VAR Exch =
            LOOKUPVALUE (
                'Exchange Rates'[RatetoGBP],
                'Exchange Rates'[Date], SDate,
                'Exchange Rates'[Currency.Symbol], CurrS
            )
        VAR summTable =
            ADDCOLUMNS (
                SUMMARIZE ( Movements, Movements[Account] ),
                "Bal",
                    CALCULATE (
                        SUMX ( movements, Movements[Amount.Base] * Exch ),
                        ALL ( 'Calendar' ),
                        'Calendar'[Date] <= SDate
                    )
            )
        RETURN
            SUMX ( summTable, [Bal] )

This returns the correct amount for each account, translated at the year opening rate. However the column totals are incorrect. It is clearly something to do with the way in which the exchange rate is being calculated on the totals, but I thought I had got over than with my SUMX s. I have tried to change the VAR CurrS to not use MAX, but use VALUES, but this falls down on a lack of row context and an error saying one value cannot be returned. 

I know I must be doing something daft; does anyone have any suggestions?

[Anonymous]

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]

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]

[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.

[Mishutka]

OK, I understand that, but how do you suggest I do that? This is what I was trying to achieve with the SUMXs. What is odd, is that the exchange rate is correct for each account and it delivers the correct number - what is incorrect is the total. This is I think while it is correct row by row, in the row context of the total the VAR CurrS returns a value that is incorrect and instead of summing the (correct) row totals, the total performs the calculation with the wrong exchange rate