Flagging and Summing Changes (Expansion/Contraction) for Recurring Donations MoM

[samdep]

Hi PBI Community!

I have a table of revenue data, similar to the below - and I would like to flag customer IDs who've either lessened or grown their recurring donation, MoM. If if their spend has stayed the same, I want to disregard them. I also have one-time only donor data mixed into this table, so I also need to disregard them.

My goal off the below table is (either via a measure or conditional column) to show a count of customers whose spend grew MoM (in this case, 1 - Customer A), the count of customers whose spend contracted MoM (in this case, 1 - Customer B), as well as the total additional dollars (+$5 from Cust A going from $5 to $10 donation) and the total lost dollars (-$5 from Cust B, who went from $10 to $5).

Cust C can be disregarded, given his spend remained the same and Customer D should be disregarded overall because he is a one-time only donor.

Customer ID   Revenue   CloseDate     Status

A                     $5             1/1/2021      Recurring

B                     $10            1/1/2021     Recurring

C                     $20            1/1/2021     Recurring

A                     $10             2/1/2021    Recurring

B                     $5               2/1/2021    Recurring

C                     $20             2/1/2021    Recurring

D                     $120           1/1/2021    One-Time

Many thanks in advance for any and all help!! 

[v-rongtiep-msft]

Hi @samdep ,

Please have a try.

Create two measures.

Last_month = CALCULATE(MAX('Table'[Revenue]),DATEADD('Table'[CloseDate],-1,MONTH))

answer = 
var choose = SELECTEDVALUE('Table'[Revenue])
VAR RESULT=CALCULATE(choose-'Table'[Last_month],FILTER(ALL('Table'),'Table'[Customer ID]=SELECTEDVALUE('Table'[Customer ID])&&'Table'[Status]=SELECTEDVALUE('Table'[Status])))
VAR SS= IF(MAX('Table'[Status])="One-Time"||RESULT=0,BLANK(),RESULT)
return SS

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samdep]

@v-rongtiep-msft Thank you! I am most of the way there, but for some reason, the change in the donation is not being reflected in the right month. You can see in the image below, it's reflected as +$4.25 in April, but April was the first donation month. It's also reflected in May, but on a line where the opportunity is closed-lost (failed transaction, which I probably need to filter out). Any thoughts as to why it's not reflecting on the $30 line for May? It's concerning because if I sum for expansion revenue, it will look like $4.25 was expansion revnue in April as well.

My Measures:

LAST_MONTH = CALCULATE(MAX(Opp_FunraiseSubscription[Amount]), DATEADD('Calendar'[Date],-1,MONTH))

EXPANSION/CONTRACTION =

VAR CHOOSE = SELECTEDVALUE(Opp_FunraiseSubscription[Amount])

VAR RESULT = CALCULATE([LAST_MONTH] - CHOOSE, FILTER(ALL(Opp_FunraiseSubscription), Opp_FunraiseSubscription[AccountId] = SELECTEDVALUE(Opp_FunraiseSubscription[AccountId]) && Opp_FunraiseSubscription[Status] = SELECTEDVALUE(Opp_FunraiseSubscription[BATTALION MEMBER])))

VAR OUTCOME = IF(MAX(Opp_FunraiseSubscription[Status]) = "One-Time" || RESULT = 0, BLANK(), RESULT)

RETURN OUTCOME

[Ashish_Mathur]

Hi,

Could you take a more comprehensive example with all possible entries you can have in the Status column and then show the expected result.

[samdep]

Hi, @Ashish_Mathur ,

Yes, no problem! Thank you for your help! I've pasted in a sample dataset below. These transactions are all from the same donor. The date field is the date the transaction processed and the amount is the donation amount.

The stagename field indicates whether the transaction was successfully processed (closed-won). As you can see, there is a $25.75 donation that is closed-lost on 5/11 (due to an expired credit card or whatnot), but the donor realized this and re-upped at a higher amount of $30 on 5/17.

My goal would be to exclude the failed transaction, and note Last_Month's (April) amount of $25.75 on the 5/17 line and show the increase of +$4.25.

I'm using KPI cards to show the total number of donors (in a given month) that have expanded or contracted their donation, as well as the sum of those dollars.

One additional caveat is that this particular donor made her usual recurring donation of $30 on 8/17, but also gave an additional $104 on 8/17 and 8/20. These are denoted as one-time only donations, so they should not factor into the expansion or contraction calculations.

This is the setup of the data that I am working with. My CloseDate (Date) field is tied to the Date field in my Calendar table. I had been getting errors about contiguous dates, so I moved to using my Calendar Date field instead, which seemed to resolve the issue.

Many, many thanks in advance for any and all guidance! 

Donor	Date	Amount	StageName	Status
Donor A	4/11/2021 0:00	25.75	Closed Won	Recurring
Donor A	5/11/2021 0:00	25.75	Closed Lost	Recurring
Donor A	5/17/2021 0:00	30	Closed Won	Recurring
Donor A	6/17/2021 0:00	30	Closed Won	Recurring
Donor A	7/17/2021 0:00	30	Closed Won	Recurring
Donor A	8/17/2021 0:00	30	Closed Won	Recurring
Donor A	8/17/2021 0:00	104	Closed Won	One-Time
Donor A	8/20/2021 0:00	104	Closed Won	One-Time
Donor A	9/17/2021 0:00	30	Closed Won	Recurring
Donor A	10/17/2021 0:00	30	Closed Won	Recurring
Donor A	11/17/2021 0:00	30	Closed Won	Recurring

[Ashish_Mathur]

Hi,

What exact result are you expecting?  Is Status already an input column in your dataset?

[samdep]

Hi @Ashish_Mathur -

My goal result would be similar to the below. Yes, status is already an existing column in the table. Ultimately, I would like to be able to count the number of unique donors who have grown their donation from one payment to another (or reduced their donation) and sum the total dollars of "expansion" revenue. So, if this were the only donor I had, I would show that I have 1 unique donor who would fall under 'Expansion Revenue' at a total of $4.25. 

Conversely, if Donor A had reduced her donation, I would flag her as a 'Contraction' Revenue Donor at -$4.25. 

My aim to show it in a tabular format and then, in KPI cards (total unique donors who have grown their donation v. total unique donors who have contracted their donation). If I can identify these individuals, especially those reducing their donation, I can potentially get a sense of who is more likely to churn, etc.

It seems like what @v-rongtiep-msft works perfectly, for some reason though - the change in revenue is not being reflected in the appropriate month in my table. +$4.25 was added in May, rather than April and it's showing as an April addition in my table

Donor	Date	Amount	StageName	Status	Change From Last Donation	Expansion/Contraction
Donor A	4/11/2021 0:00	25.75	Closed Won	Recurring	N/A bc this is the 1st Donation
Donor A	5/11/2021 0:00	25.75	Closed Lost	Recurring	Disregard this transaction bc it failed/closed-lost.
Donor A	5/17/2021 0:00	30	Closed Won	Recurring	+$4.25	Expansion
Donor A	6/17/2021 0:00	30	Closed Won	Recurring	$0
Donor A	7/17/2021 0:00	30	Closed Won	Recurring	$0
Donor A	8/17/2021 0:00	30	Closed Won	Recurring	$0
Donor A	8/17/2021 0:00	104	Closed Won	One-Time	Disregard this transaction bc it's not recurring.
Donor A	8/20/2021 0:00	104	Closed Won	One-Time	Disregard this transaction bc it's not recurring.
Donor A	9/17/2021 0:00	30	Closed Won	Recurring	$0
Donor A	10/17/2021 0:00	30	Closed Won	Recurring	$0
Donor A	11/17/2021 0:00	30	Closed Won	Recurring	$0

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas

Previous amount = if(or(Data[StageName]="Closed Lost",Data[Status]="One-Time"),BLANK(),LOOKUPVALUE(Data[Amount],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Donor]=EARLIER(Data[Donor])&&Data[Date]<EARLIER(Data[Date])&&Data[StageName]="Closed Won"&&Data[Status]="Recurring")),Data[Donor],Data[Donor],Data[Status],"Recurring"))

Expansion/contraction = if(or(ISBLANK([Previous amount]),Data[Amount]-Data[Previous amount]=0),BLANK(),if(Data[Amount]-Data[Previous amount]>0,"Expansion","Contraction"))

Hope this helps.