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