topic Re: New and Returning Customers in Quick Measures Gallery

New and Returning Customers

marcorusso — Wed, 03 May 2017 05:16:15 GMT

Name:

New customers

Description:

Calculate a measure filtering only the new customers in the selected period

Parameters:

Name: Base value

Tooltip: The value you want to calculate

Type: Numerical field / measure

Name:FactTable

Tooltip: The table containing the data used in Base value

Type: Table

Name: DateOfTransaction

Tooltip: The column containing the date of the transaction in the fact table (see FactTable)

Type: Column

Name: DateColumn

Tooltip: The column Date in the Date table

Type: Column

Name: CustomerKeyColumn

Tooltip: The column containing the customer identified in the customers table

Type: Column

{Base value} New Customers =
        VAR FirstPurchaseCustomers =
            ADDCOLUMNS (
                ALL ( {CustomerKeyColumn} ),
                "DateOfFirstBuy", CALCULATE ( MIN ( {DateTransaction} ), ALLEXCEPT ( {FactTable}, {CustomerKeyColumn} ) )
            )
        VAR NewCustomers =
            FILTER (
                FirstPurchaseCustomers,
                CONTAINS ( VALUES ( {DateColumn} ), {DateColumn}, [DateOfFirstBuy] )
            )
        RETURN
            CALCULATE (
                {Base value},
                NewCustomers
            )

Example:

Sales New Customers =
        VAR FirstPurchaseCustomers =
            ADDCOLUMNS (
                ALL ( Sales[CustomerKey] ),
                "DateOfFirstBuy", CALCULATE ( MIN ( Sales[Order Date] ), ALLEXCEPT ( Sales, Sales[CustomerKey] ) )
            )
        VAR NewCustomers =
            FILTER (
                FirstPurchaseCustomers,
                CONTAINS ( VALUES ( 'Date'[Date] ), 'Date'[Date], [DateOfFirstBuy] )
            )
        RETURN
            CALCULATE ( [Sales Amount], NewCustomers )

eyJrIjoiODhiNTg0YzAtNmU5MC00YTBjLTlmODUtZmY5ZTJkYTE0MjcxIiwidCI6ImY1NDViZDY2LTdjM2YtNDcyOS04NTFhLWI3Y2EzYWM5ZmI2ZSIsImMiOjh9

Re: New and Returning Customers

Anonymous — Mon, 29 May 2017 15:37:20 GMT

Great Measure, thnx!

Is it possible to make it so that filters can also be applied?

Re: New and Returning Customers

tringuyenminh92 — Fri, 06 Oct 2017 06:46:24 GMT

Hi marcorusso,

i dont understand meaning of CONTAINS ( VALUES ( 'Date'[Date] ), 'Date'[Date], [DateOfFirstBuy] ), could you describe the underlying of it?

Re: New and Returning Customers

marcorusso — Fri, 06 Oct 2017 07:57:22 GMT

It checks whether the [DateOfFirstBuy] is in the list of dates included in the current filter context.

Marco Russo - SQLBI

Re: New and Returning Customers

Anonymous — Fri, 06 Oct 2017 15:21:19 GMT

The addition of the quick measure into the October release brought me to your version...

What I would like to do is see a to-date or year to-date view of how the new sales compares to all sales but my attempts so far have floundered. i.e. a running total of new sales

I might also want to see how many sales we've made for new customers in year i.e. customer is seen as new from a certain date to another date rather than just a point in time. If I drill up to years I know I can get that figure but not see a trend by period.

Re: New and Returning Customers

belmore — Mon, 09 Oct 2017 21:42:53 GMT

The Grand totals for the [Sales New Customers] column equal the Grand totals for the [Sales Amount].....what can I do to get the GrandTotals for [Sales New Customers] to equal -- the actual GrandTotals for the column? Thanks

Re: New and Returning Customers

CAPEconsulting — Sun, 15 Oct 2017 05:14:01 GMT

Hey marcorusso,

Would this approach work fine - so without VARIABLE function and also a bit smaller code

New Clients = COUNTROWS (FILTER (CALCULATETABLE (ADDCOLUMNS (VALUES ( Episodes[client_key] ),"DateOfFirstService", CALCULATE ( MIN ( 'Service Contacts'[Service Date] ) )), ALL ( 'Service Contacts'[Service Date] )), CONTAINS (VALUES ( 'Service Contacts'[Service Date] ), 'Service Contacts'[Service Date], [DateOfFirstService] )))

Your fan :smileyhappy:,

Abhijeet

Re: New and Returning Customers

marcorusso — Sun, 15 Oct 2017 10:04:28 GMT

If you need to use older versions of DAX, avoiding variables makes sense. Otherwise, smaller code doesn't mean neither simpler nor faster code. Please remind that variables can help the engine to get more optimized query plans, avoiding to evaluate the same expression multiple times.

It is not necessarily the case for this formula, but it could be in a complex query and in general it is a best practice.

Marco Russo - SQLBI

Re: New and Returning Customers

belmore — Thu, 19 Oct 2017 17:44:18 GMT

Thank you for your help. I do not understand why the yearly subtotals do not sum correctly.

For example: Year 2007

The martix shows the following Sums for 2007

Customers | New Customers | Returning Customers

-1409 -1409 -0

It seems like the Sums should equal the following

Customers | New Customers | Returning Customers

-1498 -1409 -89

Thank you for your help.

Re: New and Returning Customers

lcasey — Tue, 07 Nov 2017 00:01:26 GMT

I agree,

The sums are wrong and make no sense. This calculation should never be used in real world scenarios.

Hey, If I calculate 1 +1 it equals 5

Thats exactlly what you are teaching here...:)

Re: New and Returning Customers

jason435 — Tue, 02 Jan 2018 11:32:32 GMT

I thought that was an error at first as well.

It depends on how you interpret the meaning of subtotal. Once you think about it, the only way that makes sense is what Marco did.

The subtotal row is filtered for the entire period above it. 2007 was the first year, so all customers are new customers in 2007 by definition. If the return customers subtotal was the total of each month's returning customers, new plus returning customers would be more than total customers. That wouldn't make sense.

So would the student lcaseylike to take over and show the class a better solution? :smileywink:

Re: New and Returning Customers

kirko — Sun, 27 May 2018 05:05:12 GMT

What would the measure be for existing customers? or lost customers? this solves for the exact problem I am trying to solve!

thanks

Re: New and Returning Customers

Anonymous — Fri, 20 Jul 2018 20:42:05 GMT

This measure was returning more new customers than total customers until I modified this part of it:

VAR NewCustomers =
CALCULATE(
[Customers],
FILTER (
FirstPurchaseCustomers,
CONTAINS ( VALUES ( 'Date'[DateKey] ), 'Date'[DateKey], [DateOfFirstBuy] )
)
)
RETURN
NewCustomers

This now seems to be returning the correct values for me.

Re: New and Returning Customers

marcorusso — Sun, 22 Jul 2018 20:30:36 GMT

That column name should be the column used for the relationship. You are probably using DateKey instead of Date.

Re: New and Returning Customers

Anonymous — Wed, 22 Aug 2018 20:40:42 GMT

Im wondering what would you do if I want to consider New Customers to be New customer only if they dont appear in previous year only? For example 2016= Customer, 2017=Not a customer then return 2018 then I want to count this customer in 2018 as well as in 2016. How may i acheive that?

Thank you for your advice marcorusso