Forum Discussion

samdep's avatar
samdep
Icon for Advocate II rankAdvocate II
4 years ago
Solved

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

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!! 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      samdep
      Icon for Advocate II rankAdvocate II
      4 years ago

      Anonymous 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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        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.