topic Re: Optimize SUMX for Last Date per Customer in DAX Commands and Tips

Optimize SUMX for Last Date per Customer

Menar — Fri, 19 May 2023 12:30:48 GMT

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=0&rlkey=9vef4v1c89uh6b2elli1978d5

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 !

Re: Optimize SUMX for Last Date per Customer

johnt75 — Fri, 19 May 2023 13:05:32 GMT

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

Re: Optimize SUMX for Last Date per Customer

milanpasschier3 — Fri, 19 May 2023 13:06:00 GMT

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.

Re: Optimize SUMX for Last Date per Customer

tamerj1 — Fri, 19 May 2023 13:23:34 GMT

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

Re: Optimize SUMX for Last Date per Customer

Menar — Fri, 19 May 2023 14:30:58 GMT

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

Re: Optimize SUMX for Last Date per Customer

tamerj1 — Fri, 19 May 2023 15:12:08 GMT

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
)

Re: Optimize SUMX for Last Date per Customer

tamerj1 — Fri, 19 May 2023 15:12:09 GMT

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