New Customer (or returning Customer)

[AndySmith]

Hi

I am looking to identify, and also count all new customers by month in a data set.

A 'NEW' customer is someone who has not purchased in the previous complete 6 months.

There are multiple customers and products.

This is in the context of wine sales and looking at distribution points, so based on the customer AND product. If customer has been consistently purchasing Product A, and also starts purchasing Product B, then it still counts as a new customer. 

I have tried posting this a while back however solution was not quite right. I have attached some sample data. My real data will also have a date table.  

https://jovalgroup-my.sharepoint.com/:f:/g/personal/asmith_joval_com_au/Eiebi-xf3I9JkEc8JFEBqKUBM8wa...

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create dimension tables like below.

Please check the below picture and the attached pbix file.

EXCEPT function (DAX) - DAX | Microsoft Learn

INTERSECT function (DAX) - DAX | Microsoft Learn

UNION function (DAX) - DAX | Microsoft Learn

New Customer Identify: =
VAR _currentmonth =
    MAX ( 'Calendar'[Year-Month sort] )
VAR _periodstart =
    EOMONTH ( MAX ( 'Calendar'[Date] ), -7 ) + 1
VAR _periodend =
    EOMONTH ( MAX ( 'Calendar'[Date] ), 0 )
VAR _currentmonthcustomerlist =
    SUMMARIZE (
        CALCULATETABLE (
            SUMMARIZE ( Sales, Customer[Customer], 'Calendar'[Year-Month sort] ),
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year-Month sort] = _currentmonth ),
            ALL ( Customer[Customer] )
        ),
        Customer[Customer]
    )
VAR _prevcustomerlist =
    SUMMARIZE (
        CALCULATETABLE (
            SUMMARIZE ( Sales, Customer[Customer], 'Calendar'[Year-Month sort] ),
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year-Month sort] < _currentmonth ),
            ALL ( Customer[Customer] )
        ),
        Customer[Customer]
    )
VAR _newcustomerlist =
    EXCEPT ( _currentmonthcustomerlist, _prevcustomerlist )
VAR _sixmonthcustomerlist =
    SUMMARIZE (
        CALCULATETABLE (
            SUMMARIZE ( Sales, Customer[Customer], 'Calendar'[Year-Month sort] ),
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Year-Month sort] < _periodend
                    && 'Calendar'[Year-Month sort] >= _periodstart
            ),
            ALL ( Customer[Customer] )
        ),
        Customer[Customer]
    )
VAR _beforesixmonthcustomerlist =
    SUMMARIZE (
        CALCULATETABLE (
            SUMMARIZE ( Sales, Customer[Customer], 'Calendar'[Year-Month sort] ),
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year-Month sort] < _periodstart ),
            ALL ( Customer[Customer] )
        ),
        Customer[Customer]
    )
VAR _sixmonthpreviouscustomerlist =
    EXCEPT ( _beforesixmonthcustomerlist, _sixmonthcustomerlist )
VAR _comebackcustomerlist =
    INTERSECT ( _currentmonthcustomerlist, _sixmonthpreviouscustomerlist )
VAR _listall =
    SUMMARIZE (
        UNION ( _comebackcustomerlist, _newcustomerlist ),
        Customer[Customer]
    )
RETURN
    IF (
        NOT ISBLANK ( [Sales:] ),
        IF ( MAX ( Customer[Customer] ) IN _listall, 1, 0 )
    )

[AndySmith]

Thank you so much. This does seem to correctly identify new customers by product, Very helpful. What I am looking for is something more dynamic - so that ultimitley I can report on a count of these New Listings by whatever metric I have selected. This could be by salesperson, by brand, by state etc. SO the measure is robust enough to adapt to whatever data is added to the column. Example in Excel below 

[123abc]

In Power BI, you can achieve this by using DAX (Data Analysis Expressions) to create calculated columns and measures. Here is a step-by-step guide on how to identify and count new customers by month in the context of wine sales:

1. Create Relationships:

   • Ensure that you have established relationships between your tables. You should have a relationship between the sales table and the date table based on the purchase date.

2. Create a Date Table:

   • If you don't have a date table, create one in Power BI. You can use the "New Table" feature in the modeling tab and use the following DAX formula:

DateTable = CALENDAR(MIN(Sales[PurchaseDate]), MAX(Sales[PurchaseDate]))

Create Calculated Columns:

• Create a calculated column in your sales table that calculates the last purchase date for each customer and product combination. This can be done using the following DAX formula:

LastPurchaseDate = CALCULATE(MAX(Sales[PurchaseDate]), ALLEXCEPT(Sales, Sales[Customer], Sales[Product]))

Identify New Customers:

• Create a calculated column to check if a customer is new based on the 6-month criterion:

NewCustomer = IF(Sales[PurchaseDate] - Sales[LastPurchaseDate] >= 6 * 30, 1, 0)

Create Measures:

• Create a measure to count the number of new customers by month:

NewCustomersByMonth =
CALCULATE(
COUNTROWS(Sales),
FILTER(
VALUES(DateTable[Month]),
CALCULATE(SUM(Sales[NewCustomer])) > 0
)
)

1. Visualize the Data:

   • Use a table or a chart to visualize the count of new customers by month using the created measure.

Note: This assumes that your date table has a 'Month' column. Adjust the column names and relationships based on your actual data model.

Make sure to replace the table and column names with your actual data model specifics. If you encounter any issues or need further clarification, feel free to ask!

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.