Better Sales from New Customers 10-14-2022 09:55 AM - last edited 10-14-2022 09:57 AM

[Greg_Deckler]

Continuing with exploring alternatives to Power BI's default quick measures that don't involve the CALCULATE function, such as Better Running Total, Better Average per Category, Better Weighted Average per Category, and Better Filtered Value this one is for Better Sales from New Customers. 

Power BI's Sales from New Customers quick measure returns something like this:

Value for new ID = 
VAR __CURRENT_VALUES = SUMMARIZE(VALUES('Table'), 'Table'[ID])
VAR __EXISTING_VALUES =
	CALCULATETABLE(
		SUMMARIZE(VALUES('Table'), 'Table'[ID]),
		FILTER(ALL('Dates'[Date]), 'Dates'[Date] < MIN('Dates'[Date])),
		KEEPFILTERS(__CURRENT_VALUES)
	)
RETURN
	CALCULATE(SUM('Table'[Value]), EXCEPT(__CURRENT_VALUES, __EXISTING_VALUES))

This has some extremely weird and concerning syntax like the use of VALUES for one and the ridiculous way in which SUMMARIZE and VALUES are used together. Also, this measure only works at the Date level and does not roll-up to things like Month, Quarter and Year. There's a better way like:

Better Sales from New Customers = 
    VAR __CurrentCustomers = DISTINCT('Table'[ID])
    VAR __MinDate = MIN('Table'[Date])
    VAR __Table = FILTER(ALL('Table'),[Date] < __MinDate)
    VAR __PreviousCustomers = 
        DISTINCT(
            SELECTCOLUMNS(
                __Table,
                "ID",[ID]
            )
        )
    VAR __NewCustomers = EXCEPT(__CurrentCustomers, __PreviousCustomers)
RETURN
    SUMX(FILTER('Table',[ID] IN __NewCustomers),[Value])

Watch the video!

eyJrIjoiNzY3OWZjOGMtYTdiMy00ZTFkLWJjNjgtNzhlZjEwNDliYzQ0IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9