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
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.
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 )
)
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.
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
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:
Create Relationships:
Create a Date Table:
DateTable = CALENDAR(MIN(Sales[PurchaseDate]), MAX(Sales[PurchaseDate]))
Create Calculated Columns:
LastPurchaseDate = CALCULATE(MAX(Sales[PurchaseDate]), ALLEXCEPT(Sales, Sales[Customer], Sales[Product]))
Identify New Customers:
NewCustomer = IF(Sales[PurchaseDate] - Sales[LastPurchaseDate] >= 6 * 30, 1, 0)
Create Measures:
NewCustomersByMonth =
CALCULATE(
COUNTROWS(Sales),
FILTER(
VALUES(DateTable[Month]),
CALCULATE(SUM(Sales[NewCustomer])) > 0
)
)
Visualize the Data:
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
11 | |
10 | |
8 |
User | Count |
---|---|
62 | |
20 | |
11 | |
11 | |
11 |