New and Returning Customers 10-09-2017 14:42 PM

[marcorusso]

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

[Anonymous]

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. 

[marcorusso]

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

[Anonymous]

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

[kirko]

Hi

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

thanks

[belmore]

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.

[lcasey]

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...:)

[jason435]

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?

[CAPEconsulting]

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 ,

Abhijeet

[marcorusso]

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

[belmore]

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

[tringuyenminh92]

Hi @marcorusso,

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

[marcorusso]

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

Marco Russo - SQLBI

[Anonymous]

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. 

[Anonymous]

Great Measure, thnx!

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