Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AndySmith
Helper III
Helper III

New Customer (or returning Customer)

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

 

 

 

3 REPLIES 3
Jihwan_Kim
Super User
Super User

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

 

Jihwan_Kim_0-1700460041386.png

 

Jihwan_Kim_1-1700460053311.png

 

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

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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 

Capture.PNG

 

 

 

123abc
Community Champion
Community Champion

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.