Compute effective approach; sumx, summarize, sum - case of transactional sales data and exch. rates

Hi again v-yingjl ,

On the positive side, yes, now it gives an (almost) perfect calculation (missing the original correct look up of the exchange rate  closest (before) to the transaction date - but I can fix that.

On the negative side, then I believe we are back to the poor performance of the original solution of a line by line calculation (SUMX) which was the approach that proved ineffective from the beginning when working on the big dataset, i.e. original measure was;

Sales Revenue = 
SUMX(
    'Sales Revenue and Cost',
    'Sales Revenue and Cost'[Line Amount] * 
    Calculate(
        min('Exchange Rates'[Rate]),
        filter('Exchange Rates', 
        'Exchange Rates'[From Currency]='Sales Revenue and Cost'[Company Currency] && 
        'Exchange Rates'[Date]<='Sales Revenue and Cost'[Date] &&
        'Exchange Rates'[To Currency]=SELECTEDVALUE('Reporting Currency'[Currency])
        )   
    )
)​

I just tested your measure against the big dataset, and the performance is dreadfully slow and I hit again memory errors from the MS datacenter.

However, I feel you are on to "something", is there a way we can get around the line by line calculation for each transaction and aggregate to a higher level, e.g. grouped by Calendar[Year-Month] and Orders[Currency]; thinking next evolution of your last measure to something like;

Sales Revenue = 
VAR tab =
    SUMMARIZE (
        'Orders',
        'Calendar'[Year-Month],
        'Orders'[Currency],
        'Orders'[Price],
        "REp",
            CALCULATE (
                MIN ( Rates[Rate] ),
                FILTER (
                    Rates,
                    Rates[From Currency] IN DISTINCT ( Rates[From Currency] )
                        && 'Rates'[To Currency] IN DISTINCT ( ReportingCur[Currency] )
                        && 'Rates'[Date] <= MAX(Orders[Date])
                        && MAX('Rates'[Date])
                )
            )
    )
RETURN
    SUMX ( tab, [REp] * [Price] )

Still, can't hit the correct exchange rate and sum of converted price is still not right - but at least it seems to run faster as the sumx is performed on a higher aggregated level?