Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Define new and existing customer?

I would like to define 2 sets of customers to identify number of product opens per year and the type of customer they are:

Existing Customer Adds Product - these are customers who had an existing account with us previously (previous year) but then decided to open up a new product with us

New Customer - this is customers who have only opened an account this year with us but also opened a new product too

 

I have an account level table example:

 

Account CodeAccount Open Date
1234504/02/18
1238909/09/20
4324423/10/19

 

I also have a product level table example:

Account CodeProduct CodeProduct TypeProduct Open Date
1234512345REDRed09/02/18
1238912389REDRed09/11/20
4324443244BLACKBlack03/03/20

 

Based on the year and also the account date opened and the product open date i would like to define if the customer is a new or existing customer.

 

Example: If Customer A opened an account in 03/03/2019 and this customer opened a new product in 20/10/2020 this customer will be an existing customer who has a product.

 

if Customer B opened an account in 03/06/20 and this customer opened a new product within 60 days (05/07/20) this customer is a new customer

 

if customer c opened an account in 03/05/20 and this customer opens a new product after 60 days (09/12/20) this customer is an existing customer adds product

 

please could you help me on this.

thank

 

 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create a new table,

    account table = DISTINCT(UNION(VALUES('Table'[Account Code]),VALUES('Table 2'[Account Code])))

    Add a calculated column

    final column =
VAR account_date =
    CALCULATE (
        MAX ( 'Table'[Account Open Date] ),
        FILTER (
            'Table',
            'Table'[Account Code]
                = EARLIER ( 'account table'[Account Code] )
        )
    )
VAR product_date =
    CALCULATE (
        MAX ( 'Table 2'[Product Open Date] ),
        FILTER (
            'Table 2',
            'Table 2'[Account Code]
                = EARLIER ( 'account table'[Account Code] )
        )
    )
VAR year_diff =
    DATEDIFF (
        account_date,
        product_date,
        YEAR
    )
VAR day_diff =
    DATEDIFF (
        account_date,
        product_date,
        DAY
    )
RETURN
    IF (
        year_diff >= 1,
        "exsiting customer",
        IF (
            year_diff = 0
                && day_diff <= 60,
            "new customer",
            "exsiting customer"
        )
    )

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

    Create a new table,

    account table = DISTINCT(UNION(VALUES('Table'[Account Code]),VALUES('Table 2'[Account Code])))

    Add a calculated column

    final column =
VAR account_date =
    CALCULATE (
        MAX ( 'Table'[Account Open Date] ),
        FILTER (
            'Table',
            'Table'[Account Code]
                = EARLIER ( 'account table'[Account Code] )
        )
    )
VAR product_date =
    CALCULATE (
        MAX ( 'Table 2'[Product Open Date] ),
        FILTER (
            'Table 2',
            'Table 2'[Account Code]
                = EARLIER ( 'account table'[Account Code] )
        )
    )
VAR year_diff =
    DATEDIFF (
        account_date,
        product_date,
        YEAR
    )
VAR day_diff =
    DATEDIFF (
        account_date,
        product_date,
        DAY
    )
RETURN
    IF (
        year_diff >= 1,
        "exsiting customer",
        IF (
            year_diff = 0
                && day_diff <= 60,
            "new customer",
            "exsiting customer"
        )
    )

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.