Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to figure out how to calculate which of our customers are new customers, lost customers, and returning customers over a period of time. For example, over the past 4 years I want to see how many customers are new, by year/month, how many customers we have lost, by year/month, and how many are returning by year month.
The criteria is:
New customers - Customers who have not purchased anything in the entirety of the previous year in total (not year/month), but have purchased something in the "current" year/month
Lost customers - Customers who have purchased something in the entirety of the previous year, but not the "current" year/month
Returning customers - Customers who have purchased something for the entirety of the previous year, and the "current" year/month
This would need to be over multiple years, and year/months. The example below is what the data would look like if a customer has purchased something over a course of 4 year:
Customer | Posting Date | Document No |
C10000 | 1/1/2015 | SI000001 |
C10000 | 4/1/2015 | SI000002 |
C10000 | 3/16/2016 | SI000003 |
C10000 | 1/1/2018 | SI000005 |
C10001 | 1/1/2016 | SI000006 |
C10001 | 5/1/2017 | SI000007 |
C10001 | 12/4/2018 | SI000008 |
C10002 | 1/5/2018 | SI000008 |
C10003 | 1/1/2015 | SI000009 |
C10003 | 4/8/2016 | SI000010 |
C10003 | 3/15/2017 | SI000011 |
C10003 | 12/13/2018 | SI000012 |
C10004 | 1/1/2016 | SI000013 |
C10004 | 12/4/2018 | SI000014 |
Based on the data above, the customer status would be as described below for the 4 year period of time:
Customer | Customer Status |
C10000 | New customer for 2015 |
Returning customer for 2016 | |
Lost customer for 2017 | |
New customer for 2018 | |
C10002 | New customer for 2018 |
C10003 | New customer for 2015 |
Returning customer for 2016 | |
Returning customer for 2017 | |
Returning customer for 2018 | |
C10004 | New customer for 2016 |
New customer for 2018 |
Based on the two sets of data above, if I were to create a pivot table it should look like the example below:
Year | Month | |||||||||||||||||||||||||||||||||||||||||||||||
2015 | 2016 | 2017 | 2018 | |||||||||||||||||||||||||||||||||||||||||||||
Customer Status | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
Count of New Customers | 2 | 2 | 2 | 1 | ||||||||||||||||||||||||||||||||||||||||||||
Count of Lost Customers | ||||||||||||||||||||||||||||||||||||||||||||||||
Count of Returning Customers | 1 | 1 | 1 | 1 | 2 |
I am unable to attach the workbook example, so let me know if something does not make sense, or is unclear, and thank you in advance!
Solved! Go to Solution.
You can follow the below steps:
1. Create a Date table and add a relationship between Date[Date] & Purchase[Posting Date]. If you don't have one, you can use the sample logic below:
Date = VAR BaseCalendar = CALENDAR ( MIN(Purchase[Posting Date]), MAX(Purchase[Posting Date]) ) RETURN GENERATE ( BaseCalendar, VAR BaseDate = [Date] VAR YearDate = YEAR ( BaseDate ) VAR MonthNumber = MONTH ( BaseDate ) RETURN ROW ( "Year", YearDate, "Month Number", MonthNumber, "Month", FORMAT ( BaseDate, "mmm" ) ) )
2. Create the following measures:
New Customer = VAR CurrentYear = MAX ( 'Date'[Year] ) VAR CurrentMonth = MAX ( 'Date'[Month Number] ) VAR CurrentCustomers = CALCULATETABLE ( VALUES ( Purchase[Customer] ), ALL ( 'Date'[Date] )) VAR PreviousCustomers = CALCULATETABLE ( VALUES ( Purchase[Customer] ), FILTER ( ALL ( 'Date' ), 'Date'[Year] = CurrentYear - 1 || ( 'Date'[Year] = CurrentYear && 'Date'[Month Number] < CurrentMonth ) ) ) VAR NewCustomers = EXCEPT ( CurrentCustomers, PreviousCustomers ) RETURN COUNTROWS ( NewCustomers )
Lost Customers = VAR CurrentYear = MAX ( 'Date'[Year] ) VAR CurrentMonth = MAX ( 'Date'[Month Number] ) VAR CurrentYearCustomers = CALCULATETABLE ( VALUES ( Purchase[Customer] ), ALLEXCEPT ( 'Date', 'Date'[Year] ) ) VAR PreviousYearCustomers = CALCULATETABLE ( VALUES ( Purchase[Customer] ), ALL ( 'Date' ), 'Date'[Year] = CurrentYear - 1 ) VAR LostCustomers = EXCEPT ( PreviousYearCustomers, CurrentYearCustomers ) VAR IsYearGrain = NOT ( ISFILTERED ( 'Date'[Month] ) || ISFILTERED ( 'Date'[Date] ) ) RETURN IF ( IsYearGrain, COUNTROWS ( LostCustomers ) )
Returning Customers = VAR CurrentYear = MAX ( 'Date'[Year] ) VAR CurrentMonth = MAX ( 'Date'[Month Number] ) VAR CurrentCustomers = CALCULATETABLE ( VALUES ( Purchase[Customer] ), ALL ( 'Date'[Date] ) ) VAR PreviousYearCustomers = CALCULATETABLE ( VALUES ( Purchase[Customer] ), ALL ( 'Date' ), 'Date'[Year] = CurrentYear - 1 ) VAR ReturningCustomers = INTERSECT ( PreviousYearCustomers, CurrentCustomers ) RETURN COUNTROWS ( ReturningCustomers )
From you requirement, I see that Lost customers you only want to display at Year grain?
Let me know if this works for you.
You can follow the below steps:
1. Create a Date table and add a relationship between Date[Date] & Purchase[Posting Date]. If you don't have one, you can use the sample logic below:
Date = VAR BaseCalendar = CALENDAR ( MIN(Purchase[Posting Date]), MAX(Purchase[Posting Date]) ) RETURN GENERATE ( BaseCalendar, VAR BaseDate = [Date] VAR YearDate = YEAR ( BaseDate ) VAR MonthNumber = MONTH ( BaseDate ) RETURN ROW ( "Year", YearDate, "Month Number", MonthNumber, "Month", FORMAT ( BaseDate, "mmm" ) ) )
2. Create the following measures:
New Customer = VAR CurrentYear = MAX ( 'Date'[Year] ) VAR CurrentMonth = MAX ( 'Date'[Month Number] ) VAR CurrentCustomers = CALCULATETABLE ( VALUES ( Purchase[Customer] ), ALL ( 'Date'[Date] )) VAR PreviousCustomers = CALCULATETABLE ( VALUES ( Purchase[Customer] ), FILTER ( ALL ( 'Date' ), 'Date'[Year] = CurrentYear - 1 || ( 'Date'[Year] = CurrentYear && 'Date'[Month Number] < CurrentMonth ) ) ) VAR NewCustomers = EXCEPT ( CurrentCustomers, PreviousCustomers ) RETURN COUNTROWS ( NewCustomers )
Lost Customers = VAR CurrentYear = MAX ( 'Date'[Year] ) VAR CurrentMonth = MAX ( 'Date'[Month Number] ) VAR CurrentYearCustomers = CALCULATETABLE ( VALUES ( Purchase[Customer] ), ALLEXCEPT ( 'Date', 'Date'[Year] ) ) VAR PreviousYearCustomers = CALCULATETABLE ( VALUES ( Purchase[Customer] ), ALL ( 'Date' ), 'Date'[Year] = CurrentYear - 1 ) VAR LostCustomers = EXCEPT ( PreviousYearCustomers, CurrentYearCustomers ) VAR IsYearGrain = NOT ( ISFILTERED ( 'Date'[Month] ) || ISFILTERED ( 'Date'[Date] ) ) RETURN IF ( IsYearGrain, COUNTROWS ( LostCustomers ) )
Returning Customers = VAR CurrentYear = MAX ( 'Date'[Year] ) VAR CurrentMonth = MAX ( 'Date'[Month Number] ) VAR CurrentCustomers = CALCULATETABLE ( VALUES ( Purchase[Customer] ), ALL ( 'Date'[Date] ) ) VAR PreviousYearCustomers = CALCULATETABLE ( VALUES ( Purchase[Customer] ), ALL ( 'Date' ), 'Date'[Year] = CurrentYear - 1 ) VAR ReturningCustomers = INTERSECT ( PreviousYearCustomers, CurrentCustomers ) RETURN COUNTROWS ( ReturningCustomers )
From you requirement, I see that Lost customers you only want to display at Year grain?
Let me know if this works for you.
Hi. I love how elegant the code is...but I'm getting some wonky results. Note "Card Name" as my field for the unique name. Otherwise, I'm using 100% the code for the # customers that have transacted in the prior 12 mos, and 99% of the same code for customers that have transacted in the prior month (just adding an additional filter).
/12Mo_RepeatCusts_By#/:= VAR CurrentYear = MAX ( 'Date'[Year] ) VAR CurrentMonth = MAX ( 'Date'[Month Number] ) VAR CurrentCustomers = CALCULATETABLE ( VALUES ( Table1[Card Name]), ALL ( 'Date'[Date] ) ) VAR PreviousYearCustomers = CALCULATETABLE ( VALUES ( Table1[Card Name] ), ALL ( 'Date' ), 'Date'[Year] = CurrentYear - 1 ) VAR ReturningCustomers1Yr = INTERSECT ( PreviousYearCustomers, CurrentCustomers ) RETURN COUNTROWS ( ReturningCustomers1Yr )
/1Mo_RepeatCusts_By#/:= VAR CurrentYear = MAX ( 'Date'[Year] ) VAR CurrentMonth = MAX ( 'Date'[Month Number] ) VAR CurrentCustomers = CALCULATETABLE ( VALUES ( Table1[Card Name]), ALL ( 'Date'[Date] ) ) VAR PreviousMonthCustomers = CALCULATETABLE ( VALUES ( Table1[Card Name] ), ALL ( 'Date' ), 'Date'[Year] = CurrentYear, 'Date'[Month Number] = CurrentMonth - 1 ) VAR ReturningCustomers1Mo = INTERSECT ( PreviousMonthCustomers, CurrentCustomers ) RETURN COUNTROWS ( ReturningCustomers1Mo )
The results are wonky because, for many periods, I get more customer who transacted in the prior month than in the prior year. Possibly, I have messed up by 1Mo Repeat Customer adaptation. Could it be, however, that the measures above have a problem because they are absolute rather than relative? Ie they peg to a year and a prior year whereas the best measure should be 365 days or 30 days?
I apologize for not including my file. I'm using excel power pivot not PBIX and have some PII in the files
A sample data which illustrastes the issue would be helpful. Meanwhile, for getting previous month customers a better approach would be following:
VAR PreviousMonthCustomers = CALCULATETABLE ( VALUES ( Table1[Card Name] ), PREVIOUSMONTH ( 'Date'[Date] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |