New and Returning Customers

7760 Views
Highlighted
Member
Posts: 69
Registered: ‎06-24-2015

New and Returning Customers

[ Edited ]

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 )

 

Attachment
Regular Visitor
Posts: 18
Registered: ‎05-03-2016

Re: New and Returning Customers

Great Measure, thnx!

 

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

New Contributor
Posts: 501
Registered: ‎04-21-2016

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
Posts: 69
Registered: ‎06-24-2015

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

Frequent Visitor
Posts: 8
Registered: ‎10-31-2016

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
Posts: 32
Registered: ‎04-27-2016

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
Posts: 27
Registered: ‎03-18-2017

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

 

Your fan Smiley Happy,

Abhijeet

Member
Posts: 69
Registered: ‎06-24-2015

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
Posts: 32
Registered: ‎04-27-2016

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

 

Thank you for your help.

 

Member
Posts: 255
Registered: ‎09-08-2016

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