Hello,
Need help to calculate number of customers returned following 3 months after first month sale. Meaning that if a customer would have several purchases within a month, the month is what matters.
For example, C#11019
1st sale 8/16/2003 - This customer is returning because it had sales in 09,10,11
2nd - 9/5/2003 - This customer is returning - sales in 10, 11 & so on
3 - 9/14/2003
4 - 10/30/2003
5 - 11/9/2003
6 - 11/26/2003
I created calculated columns in Customers table:
BegFollowingMOafterFMS = ENDOFMONTH(Customers[1stSale Date]) +1 (returning the beginning of the month following the month of first sale)
Cons3moAfterFMS = DATE(YEAR(customers[1stSale Date]), MONTH(Customers[1stSale Date]) + 4, 1)-1
Measure to calculate returning customers:
RCTest3 =
CALCULATE(DISTINCTCOUNT(Sales[CustomerKey]),
FILTER(ALL(Sales), Sales[OrderDate] >= RELATED(Customers[BegFollowingMOafterFMS]) &&
Sales[OrderDate] <=RELATED(Customers[Cons3moAfterFMS] ) ) )
Which gave me the total of 979 but no split by month because filters do not pass from Sales to Calendar table.
Then I tried to follow the pattern from one of the EDNA classes related to customers.
RC test =
VAR CustomerList = VALUES( Sales[CustomerKey] )
VAR ChurnDateone = [BegMoAfterFMS]
VAR ChurnDatetwo = [Cons3moAfterFMS]
RETURN
COUNTROWS(
FILTER(CustomerList,
CALCULATE(COUNTROWS(Sales),
FILTER(ALL(‘Calendar’),
‘Calendar’[Date] >= ChurnDateone &&
‘Calendar’[Date] < ChurnDatetwo ) ) >0 ) )
But it didn’t return the correct result. Strangely enough while I was prepping the pbix file for upload, it returned blanks.
Please find the attached pbix file
PP3_EF.pbix (1.8 MB)