Better Sales from New Customers

Greg_Deckler — Fri, 14 Oct 2022 16:57:51 GMT

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

topic Better Sales from New Customers in Quick Measures Gallery

Better Sales from New Customers