Hello All,
I have created some formulas to identify the number of new customers and those that got reactivated.
In my logic, I created a months since first purchase to be able to find those that are new which would be months since first purchase = 0. However, it seems this formula is not working and I am not able to find what is the reason.
MOnths since last purchase = DATEDIFF([MIn date],[Max date], MONTH)
Max date =
VAR CurrentName = SELECTEDVALUE(TotalBilled[customer_id])
RETURN
MAXX(
FILTER(TotalBilled,TotalBilled[customer_id]=CurrentName),
TotalBilled[Date])
MIn date =
VAR CurrentName = SELECTEDVALUE(TotalBilled[customer_id])
RETURN
MINX(
FILTER(TotalBilled,TotalBilled[customer_id]=CurrentName),
TotalBilled[Date])
New = SUMX(
VALUES('DimCustomer'[customer_id]),
IF(ISBLANK([Prior MRR])&&NOT(ISBLANK([Current MRR]))&&[Days since last purchase]=0,[Current MRR],BLANK()))
Current MRR = sum(TotalBilled[amount_usd])
Prior MRR = CALCULATE(
SUM(TotalBilled[amount_usd]),
DATEADD( DimCalendar[FullDateAlternateKey], -1, month)
)
Reactivated =
SUMX(
VALUES('DimCustomer'[customer_id]),
IF(ISBLANK([Prior MRR])&&NOT(ISBLANK([Current MRR]))&&[Days since last purchase]>0,[Current MRR],BLANK()))
This is the sample file: https://drive.google.com/file/d/1OnDkfejfe2ow-vJoVdgANZYuSCG7-_-n/view?usp=sharing
Thank you so much in advance
