Customer reactivation formula not working

[carolinefer]

Hello All, 

I have a table with multiple measures related to churn but for some reason the Reactivated customer keeps giving me blank results. The formulas are all based on the same logic. 

Reactivated =

SUMX(

VALUES('TotalBilled (2)'[customer_id]),

IF(ISBLANK([Prior MRR])&&NOT(ISBLANK([Current MRR]))&&[Days since last purchase]>0,[Current MRR],BLANK()))

Lost =

- SUMX(

VALUES('TotalBilled (2)'[customer_id]),

IF(not ISBLANK([Prior MRR]) && ISBLANK([Current MRR]),[Prior MRR],BLANK()))

Would you kindly assist me on that one? Thanks 

[carolinefer · Accepted solution]

Hi, I was able to find my error. it was related to the days since purchase. I was using Min date but I should have used the Data that the transactions happened. 

[carolinefer]

@v-yiruan-msft  Thanks for looking into this for me. Unfortunately, the reactivation formula is still not working as well as the new. the formulas that are related to Min and Man and days since last purchase are the problem. However, I do not seem to find the error. I feel that the this formula is not picking up the days since last purchase. but the values are correct. 

Reactivated =

SUMX(

VALUES('TotalBilled (2)'[customer_id]),

IF(ISBLANK([Prior MRR])&&NOT(ISBLANK([Current MRR]))&&[Days since last purchase]>0,[Current MRR],BLANK())) I hope you are able to help me. Thanks 

[v-yiruan-msft]

Hi @carolinefer ,

If [Days since last purchase] can get the correct value, then it is possible that the conditions ISBLANK([Prior MRR]) and NOT(ISBLANK([Current MRR])) do not satisfy the requirement. But as checked the screenshot in your first post, both of them([Prior MRR] and [Current MRR]) can return the values....

Could you share a simplified pbix file with me in order to make troubleshooting and provide you a suitable solution if it is convenient? It contains the sample data (fake data) for the referenced table TotalBilled (2), all the measures mentioned in your posts and the visuals used. Please remember to remove the sensitive data from the report file before you share it. Thank you.

Best Regards

[carolinefer]

I would love to be able to share the workbook bu the sensitive info is everywhere so I am unable to remove those fields. However, what I have notice is that it could be related to the days since last purchase. Because when I put all together, it is blank. would that be the reason? 

Days since last purchase = DATEDIFF([MIn date],[Max date], MONTH)

MIn date =

VAR CurrentName = SELECTEDVALUE(TotalBilled[customer_id])

RETURN

MINX(

FILTER(TotalBilled,TotalBilled[customer_id]=CurrentName),

TotalBilled[Date])

Max date =

VAR CurrentName = SELECTEDVALUE(TotalBilled[customer_id])

RETURN

MAXX(

FILTER(TotalBilled,TotalBilled[customer_id]=CurrentName),

TotalBilled[Date])

Thanks for taking your time to check that for me. 🙂 

[v-yiruan-msft]

Hi @carolinefer ,

You can update the formula of measure [MIn date] and [Max date] as below and check whether you can get the expected result.

MIn date =
VAR CurrentName =
    SELECTEDVALUE ( TotalBilled[customer_id] )
RETURN
    MINX (
        FILTER ( ALLSELECTED ( TotalBilled ), TotalBilled[customer_id] = CurrentName ),
        TotalBilled[Date]
    )

Max date =
VAR CurrentName =
    SELECTEDVALUE ( TotalBilled[customer_id] )
RETURN
    MAXX (
        FILTER ( ALLSELECTED ( TotalBilled ), TotalBilled[customer_id] = CurrentName ),
        TotalBilled[Date]
    )

Best Regards

[amitchandak]

@carolinefer , Formula seem fine. They way to check is put all three measure in thsi formula and this formula and customer together. Check where [Prior MRR] is blank do you have non blank [Current MRR]

also check [Days since last purchase] >0

Maybe days are negative

or

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.