Forum Discussion
COUNTDISTINCT(repeatCustomers) excluding previously counted repeatCustomers
Hey v-diye-msft ,
I've actually figured out a solution to both the 'reapeat customers' and 'future repeat customers' issue. I can give a short description here:
1) Add the following date columns to the orders table
Column Name DAX PreviousOrderDate CALCULATE(MAX(Orders[Order Date]), FILTER(Orders, Orders[Customer_id]=EARLIER(Orders[Customer_id]) && Orders[Order Date]<EARLIER(Orders[Order Date])) FirstOrderDate CALCULATE(MIN(Orders[Order Date]), FILTER(Orders, Orders[Customer_id]=EARLIER(Orders[Customer_id]))TimeBetweenPurchases DATEDIFF(Orders[PreviousOrderDate], Orders[Order Date], DAY)NextOrderDate CALCULATE(MAX(Orders[Order Date]), FILTER(Orders, Orders[Customer_id]=EARLIER(Orders[Customer_id]) && Orders[Order Date]>EARLIER(Orders[Order Date]))InitialChurnDate IF(NOT(Orders[FirstOrderDate] = BLANK()), Orders[FirstOrderDate] + 365)2) Create the following measure:
Measure Name DAX repeatCustomer COUNTROWS(CALCULATETABLE(VALUES(Orders[Customer_id]), FILTER(Orders, Orders[PreviousOrderDate] = Orders[FirstOrderDate] && Orders[TimeBetweenPurchases] <= 365)))For summarizing repeat customers use the InitialChurnDate as date aggregator. This way, it shows which customers turn into repeat customers (i.e., having bought a second time between their first purchase and exactly 1 year after that).
For summarizing future repeat customers use the regular order date as aggregator (From the customers who ordered on that date it checks to see if it is the second order. If it is the second order, and is within a year from the first order, the customer will be counted as future repeat customer (i.e., the customer automatically becomes a repeat customer on the date exactly a year from the their first purchase).I know these are kind of awkward definitions for repeat/returning customers, but that's what I have to work with for now...
I consider this question answered, thanks for the help. Still, if anyone finds a more elegant solution (one that doesn't require 4 extra date columns), I would love to hear!
Hi Anonymous
you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.
Hey v-diye-msft ,
I've actually figured out a solution to both the 'reapeat customers' and 'future repeat customers' issue. I can give a short description here:
1) Add the following date columns to the orders table
| Column Name | DAX |
| PreviousOrderDate | CALCULATE(MAX(Orders[Order Date]), FILTER(Orders, Orders[Customer_id]=EARLIER(Orders[Customer_id]) && Orders[Order Date]<EARLIER(Orders[Order Date])) |
| FirstOrderDate | CALCULATE(MIN(Orders[Order Date]), FILTER(Orders, Orders[Customer_id]=EARLIER(Orders[Customer_id])) |
| TimeBetweenPurchases | DATEDIFF(Orders[PreviousOrderDate], Orders[Order Date], DAY) |
| NextOrderDate | CALCULATE(MAX(Orders[Order Date]), FILTER(Orders, Orders[Customer_id]=EARLIER(Orders[Customer_id]) && Orders[Order Date]>EARLIER(Orders[Order Date])) |
| InitialChurnDate | IF(NOT(Orders[FirstOrderDate] = BLANK()), Orders[FirstOrderDate] + 365) |
2) Create the following measure:
| Measure Name | DAX |
| repeatCustomer | COUNTROWS(CALCULATETABLE(VALUES(Orders[Customer_id]), FILTER(Orders, Orders[PreviousOrderDate] = Orders[FirstOrderDate] && Orders[TimeBetweenPurchases] <= 365))) |
For summarizing repeat customers use the InitialChurnDate as date aggregator. This way, it shows which customers turn into repeat customers (i.e., having bought a second time between their first purchase and exactly 1 year after that).
For summarizing future repeat customers use the regular order date as aggregator (From the customers who ordered on that date it checks to see if it is the second order. If it is the second order, and is within a year from the first order, the customer will be counted as future repeat customer (i.e., the customer automatically becomes a repeat customer on the date exactly a year from the their first purchase).
I know these are kind of awkward definitions for repeat/returning customers, but that's what I have to work with for now...
I consider this question answered, thanks for the help. Still, if anyone finds a more elegant solution (one that doesn't require 4 extra date columns), I would love to hear!