Formulas created for for downgrade, reactivated, lost, upgrade and downgrade does not work

[carolinefer]

Hello All, 

The formulas I have created is not working, neither for count of IDs or Sum of Revenue. They give me the correct value only for the last month. 

Here are the definitions: 

Lost = Customer that did not purchase current month and had purchased previous month. 

Downgrade = Customer that purshased less this month compared to previous month. 

Upgrade = Customer that purshased more this month compared to previous month. 

New = Customer that had never purchased and had the first purchase in current month

Reactivated = customer that purchased this month but did not purchase last month, however, they have purchased in any time apart from last month and current month. 

Here are the formulas that I have created : 

Current MRR = SUM(TotalBilled[amount_usd])

Prior MRR = CALCULATE(

SUM(TotalBilled[amount_usd]),

DATEADD( DimCalendar[FullDateAlternateKey], -1, month)

)

Max date =

VAR CurrentName = SELECTEDVALUE(TotalBilled[customer_id])

RETURN

MAXX(

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

TotalBilled[Date])

First MRR =

VAR CurrentName = SELECTEDVALUE(TotalBilled[customer_id])

RETURN

MINX(

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

TotalBilled[Date])

Days since last purchase = VALUE([Max date]-[First MRR])

New = SUMX(

VALUES(TotalBilled[customer_id]),

IF ( [Days since last purchase] = 0 && not( ISBLANK([Current MRR])),[Current MRR],BLANK())

)

New count = SUMX(

VALUES(TotalBilled[customer_id]),

IF ( [Days since last purchase] = 0 && not( ISBLANK([Current MRR])),[Current Customer],BLANK())

)

 Lost =

-SUMX(

VALUES(TotalBilled[customer_id]),

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

Lost count =

-SUMX(

VALUES(TotalBilled[customer_id]),

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

 Downgrade = SUMX(

VALUES(TotalBilled[customer_id]),

IF(NOT(ISBLANK([Prior MRR]))&&NOT(ISBLANK([Current MRR]))&&([Current MRR]<[Prior MRR]),[Current MRR] - [Prior MRR],BLANK()))

 Downgrade count = SUMX(

VALUES(TotalBilled[customer_id]),

IF(NOT(ISBLANK([Prior MRR]))&&NOT(ISBLANK([Current MRR]))&&([Current MRR]<[Prior MRR]),[Current Customer],BLANK()))

Upgrade = SUMX(

VALUES(TotalBilled[customer_id]),

IF(NOT(ISBLANK([Prior MRR]))&&NOT(ISBLANK([Current MRR]))&&([Current MRR]>[Prior MRR]),[Current MRR] - [Prior MRR],BLANK()))

Upgrade count = SUMX(

VALUES(TotalBilled[customer_id]),

IF(NOT(ISBLANK([Prior MRR]))&&NOT(ISBLANK([Current MRR]))&&([Current MRR]>[Prior MRR]),[Current Customer],BLANK()))

Thanks in advance

[parry2k · Accepted solution]

@carolinefer check this post which talks in detail about something similar, tweak it as you see fit.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡