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

[LasseL]

Dear community,

Main issue; finding the most compute effective approach.

Current Datamodel;

Background info;

• Sales Revenue and Cost is transactional data, each line representing information about actual sales at a particular date.
• Sales Revenue lines are in different currencies.
• Table has approx. 11 million lines.
• Exchange Rates contains information about Currency Exchange Rates from and to currency at particular dates. Date is a "valid from date", and is not consistent in terms of an exchange rate for all dates in Sales Revenue and Cost (most commonly only last date of each month).

Task;

1. Establish a consolidated report in a consolidate currency by choice of the user.
2. Use latest valid currency exchange rate "before" Sales Revenue and Cost" Date.

My first approach;

• Solution; Using Power Query with Table.SelectRows to add columns to Sales Revenue... table with selected reporting currencies and calculated amount based on that. I.e. perform the transformation at the time of loading the data.

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Max(Table.SelectRows(Rates, (Rate) => Rate[Date]<=[Date] and Rate[Currency]=[Currency]),"Date")
),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Rate"}, {"Rate"})​

• Problem; with a clean load Power BI services could load around 100k lines per second, with above solution it was reduced to only 10 lines per second and with 11 million lines it would take days to finish.

Second approach;

• Solution; Using DAX for the transformation via pure SUMX(Sales Revenue...) with calculate conditions for the conversion

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])
        )   
    )
)​

• Problem; Solutions provides the perfect answer and best feature of being dynamically able to select "another" reporting currency, however, publishing this to services it is evident that it is very ineffective, slow and at times power bi services post an error stating too much memory consumption - i.e. not a "production able" solution.

Third approach, and where I am stuck:

• Solution; I've read that SUMX is not to be used like I did cause it does a calculation for every single row every time, and that people have come around it by using SUMX VALUES, summarize or a combination. I am at the point of trying out SUMX VALUES but can't get it to play out correct and am wondering if it is the right solution at all, 2 measures combined to "Sales Revenue";

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

• Also I am concerned if the method secures that I am using the correct currency exchange rate "before" the Sales Revenue posting date - as a minimum I would like that a monthly level of summarized amount it is translated by the exchange rate "before".

I am stuck, quite beginners level and have spent so many hours to the point of much frustration - any hint of the right direction would be greatly appreciated!

Best regards,

Lasse