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,
Why not count it agains a specific day in a calendar?
- Create a calender with New Table -> Table = CALENDER(Mindate,Maxdate)
- Create a calculated column in this date table
Cust_this_day =
CALCULATE(DISTINCTCOUNT(order_table[customer_id]);
FILTER(order_table;order_table[order_date] = date_table[date])
) - You now can create a visual using the date hierarchy (year, month, q, day) to summarize your results, use MAX - not SUM.
Hey Tijn,
Thanks for your response. I believe your solution gives me a peak number of customers in a particular time period. However, I'm looking for counts of repeat/returning/recurring customers after their first purchase, counted directly 1 year after their first purchase.