cancel
Showing results for
Did you mean:

# New and Returning Customers

Member
18562 Views
Member

## New and Returning Customers

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 =
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 =
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 )

Regular Visitor

## Re: New and Returning Customers

Great Measure, thnx!

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

New Contributor

## Re: New and Returning Customers

Hi @marcorusso,

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

Member

## Re: New and Returning Customers

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

Marco Russo - SQLBI

Highlighted
Frequent Visitor

## Re: New and Returning Customers

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.

Regular Visitor

## Re: New and Returning Customers

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

Regular Visitor

## Re: New and Returning Customers

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] )))

Abhijeet

Member

## Re: New and Returning Customers

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

Regular Visitor

## Re: New and Returning Customers

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

Member

## Re: New and Returning Customers

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