How to define new and existing customers on Power Bi using DAX?

Question

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 Code    Account Open Date
12345           04/02/18
12389           09/09/20
43244           23/10/19

I also have a product level table example:

Account Code    Product Code    Product Type    Product Open Date
12345           12345RED        Red             09/02/18
12389           12389RED        Red             09/11/20
43244           43244BLACK      Black           03/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 in a new column.

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

Answer 1

As a general Business Intelligence applied to Power BI, to slice-and-dice your facts based on whether the Customer was Existing or New on the Event date, you need to defined an attribute on the Fact table itself.

Going one step further, the practical implementation consists of:

1. define a new dimension: Customer Type with an attribute Type: {Existing, New}.
2. in the back-end, add the Foreign Key of this new dimension Customer Type to your Fact
3. in the Front-end use Customer Type dimension to slice-and-dice over your facts based on whether the customer is Existing or New