Optimize SUMX for Last Date per Customer

[Menar]

Hello Everyone,

I have been working on a measure which only shows the sum of 'Sales' per customer but only for their last transaction date in the last 3 months.
The last 3 months are computed from a user selection in a date slicer.

I have the following code which does work, however i am a bit concerned about performance issues as my fact table will be quit large (about 10 million rows).
Any tips on how to improve the performance ? I do not know if the nested CALCULATE has a huge impact.
I have already tried to put variables as much as possible.

LatestSalesbyCustomer = 
var selecteddate = MAX('Date Table'[Date])

var Last3MonthsDates = DATESINPERIOD(
    'Secondary Date Table'[Date],
    referencedate,
    -3,
    MONTH)

var CustomerInTheLast3Months =
    CALCULATETABLE(
                    VALUES('Fact Table'[Customerid]),
                    REMOVEFILTERS('Date Table'[Date]),
                    Last3MonthsDates,
                    USERELATIONSHIP('Date Table'[Date], 'Secondary Date Table'[Date])
                    )

var result =
SUMX(
CustomerInTheLast3Months,
    CALCULATE(
                CALCULATE(
                            SUM('Fact Table'[Sales]),
                            var latestdate = CALCULATETABLE(
                                                        LASTDATE('Fact Table'[Transaction Date]),
                                                        REMOVEFILTERS('Fact Table'),
                                                        VALUES('Fact Table'[Customerid]),
                                                        Last3MonthsDates)
                            return
                            latestdate)
                        ,
                    REMOVEFILTERS('Date Table'[Date]),
                    Last3MonthsDates,
                    USERELATIONSHIP('Secondary Date Table'[Date],'Date Table'[Date])
            )
)
return result

Link to PBI File : https://www.dropbox.com/scl/fi/sqtgaait9x0psmj7eg72n/Database-sum-sales-by-cust-for-max-date.xlsx?dl...

Link to Source Data = https://www.dropbox.com/s/ugileocfavvvtty/Sales%20for%20last%20date%20per%20customer.pbix?dl=0

Thanks in advance for any help !

[Menar]

@johnt75  thanks, I was thinking about addcolumn but your version does not work if I select a date in the date slicer.

@milanpasschier3 Thanks but I do not want to do it in Python

@tamerj1 thank you but the total is wrong in your version

[tamerj1]

@Menar

Yes, I noticed that but I was in hurry just leaving the office. Sorry, I don't have access to my laptop now to test but I expect this could work

LastSalesByCustomer TJ =
VAR CurrentDate =
MAX ( 'Date Table'[Date] )
VAR SelectedProducts =
ALLSELECTED ( 'Product' )
RETURN
SUMX (
SUMMARIZE ( 'Fact Table', Customer[Customer ID], 'Product'[Product ID] ),
VAR T1 =
CALCULATETABLE ( 'Fact Table', ALL ( 'Date Table' ) )
VAR T2 =
CALCULATETABLE ( 'Fact Table', ALL ( 'Date Table' ), SelectedProducts )
VAR T3 =
FILTER ( T2, 'Fact Table'[Transaction Date] < CurrentDate )
VAR MaxDate =
MAXX ( T3, 'Fact Table'[Transaction Date] )
VAR T4 =
FILTER ( T1, 'Fact Table'[Transaction Date] = MaxDate )
VAR Result =
SUMX ( T4, 'Fact Table'[Sales] )
RETURN
Result
)

[tamerj1]

@Menar

Yes, I noticed that but I was in hurry just leaving the office. Sorry, I don't have access to my laptop now to test but I expect this could work

LastSalesByCustomer TJ =
VAR CurrentDate =
MAX ( 'Date Table'[Date] )
VAR SelectedProducts =
ALLSELECTED ( 'Product' )
RETURN
SUMX (
SUMMARIZE ( 'Fact Table', Customer[Customer ID], 'Product'[Product ID] ),
VAR T1 =
CALCULATETABLE ( 'Fact Table', ALL ( 'Date Table' ) )
VAR T2 =
CALCULATETABLE ( 'Fact Table', ALL ( 'Date Table' ), SelectedProducts )
VAR T3 =
FILTER ( T2, 'Fact Table'[Transaction Date] < CurrentDate )
VAR MaxDate =
MAXX ( T3, 'Fact Table'[Transaction Date] )
VAR T4 =
FILTER ( T1, 'Fact Table'[Transaction Date] = MaxDate )
VAR Result =
SUMX ( T4, 'Fact Table'[Sales] )
RETURN
Result
)

[tamerj1]

Hi @Menar 
Please refer to attached sample file amended qith the proposed solution

LastSalesByCustomer TJ = 
VAR CurrentDate = 
    MAX ( 'Date Table'[Date] )
VAR T1 = 
    CALCULATETABLE ( 'Fact Table', ALL ( 'Date Table' ) )
VAR T2 = 
    CALCULATETABLE ( 'Fact Table', ALL ( 'Date Table' ), ALLSELECTED ( 'Product' ) )
VAR T3 =
    FILTER ( T2, 'Fact Table'[Transaction Date] < CurrentDate )
VAR MaxDate = 
    MAXX ( T3, 'Fact Table'[Transaction Date] )
VAR T4 =
    FILTER ( T1, 'Fact Table'[Transaction Date] = MaxDate )
VAR Result =
    SUMX ( T4, 'Fact Table'[Sales] )
RETURN 
    Result

[milanpasschier3]

Hey man, is it okay to do this in Python via Power Query? This may reduce performance time significant as the calculations are being made before displaying the data in PBI. If so, I can have look this week.

[johnt75]

You can try

Latest Sales = VAR selecteddate = MAX( 'Date Table'[Date] )
VAR Last3MonthsDates =
	DATESINPERIOD(
		'Secondary Date Table'[Date],
		selecteddate,
		-3,
		MONTH
	)
VAR CustomersWithDates =
	CALCULATETABLE(
		ADDCOLUMNS(
			VALUES( 'Fact Table'[Customerid] ),
			"@last sale",
				CALCULATE( MAX( 'Fact Table'[Transaction Date] ) )
		),
		Last3MonthsDates,
		REMOVEFILTERS( 'Date Table' ),
		USERELATIONSHIP( 'Date Table'[Date], 'Secondary Date Table'[Date] )
	)
VAR Result =
	CALCULATE(
		SUM( 'Fact Table'[Sales] ),
		TREATAS(
			CustomersWithDates,
			'Fact Table'[Customerid],
			'Fact Table'[Transaction Date]
		)
	)
RETURN
	Result