I am trying to create DAX expressions to determine the percentage of customers that returned 90 days after their first purchase and also determine the percentage of customers that returned 3 months after the first 90 days.
There is a Customers Table which has the following columns: CustomerKey, DateFirstPurchase and there is a Sales Table which has the following columns: CustomerKey, OrderDate.
Below is a very sample of the data in each table and the end result expected from the full data to be a reference for the ask. I have been able to determine the first 2 columns of the expected results, but need help on the percentages in the 3rd and 4th columns. Thanks!
CUSTOMERS TABLE
CustomerKey DateFirstPurchase
25640 8/1/03 0:00
37688 8/1/03 0:00
26192 8/1/03 0:00
33962 8/1/03 0:00
24329 8/1/03 0:00
34293 8/1/03 0:00
33556 8/1/03 0:00
39079 8/1/03 0:00
32330 8/1/03 0:00
25507 8/1/03 0:00
24567 8/1/03 0:00
21746 8/1/03 0:00
30200 8/1/03 0:00
34940 8/1/03 0:00
38788 8/1/03 0:00
37659 8/1/03 0:00
26338 8/1/03 0:00
31407 8/1/03 0:00
32284 8/1/03 0:00
22204 8/1/03 0:00
25856 8/1/03 0:00
33941 8/1/03 0:00
29344 8/1/03 0:00
26982 8/1/03 0:00
25703 8/1/03 0:00
SALES Table
CustomerKey OrderDate
25640 8/21/03 0:00
25640 8/21/03 0:00
25640 8/21/03 0:00
26192 4/24/04 0:00
26192 10/28/02 0:00
24329 6/22/04 0:00
24329 6/22/04 0:00
25507 10/7/03 0:00
24567 9/21/03 0:00
24567 9/21/03 0:00
21746 5/24/04 0:00
21746 5/24/04 0:00
26338 10/13/02 0:00
26338 2/29/04 0:00
26338 2/29/04 0:00
RESULT FOR FULL DATA AS A REFERENCE
Edit:
I've created a Calendar Table using the following expression:
Calendar = CALENDAR(FIRSTDATE(Sales[OrderDate]), LASTDATE( Sales[OrderDate]))
I've also tried the following for the % of customers who returned about 90 days and did not get the correct result, but I feel like it's on the right track:
Purchase within 90days(%) =
VAR Customerlist = VALUES( Sales[CustomerKey] )
VAR Returndate = 90
VAR CountCustomers =
COUNTROWS(
FILTER(
Customerlist,
CALCULATE( COUNTROWS (Sales),
FILTER( ALLSELECTED ( 'Calendar' ),
'Calendar'[Date] > ( MIN ( Sales[OrderDate] ) ) &&
'Calendar'[Date] < MIN ( Sales[OrderDate]) + Returndate
)
) >0
)
)
Return
DIVIDE(CountCustomers,[Customers])