Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

New + Lost Customer Analysis (based on Recurring Revenue)

Hi, Been working this for 2 days straight and read countless forum posts and YT vids - no joy still. I have a Revenue dataset that contains recurring customer revenue - not sales (same customer...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    Use the following two measures:

     

    New Customer Count = COUNTROWS(EXCEPT(VALUES(REVENUE[CUSTOMER_NUMBER]),CALCULATETABLE(VALUES(REVENUE[CUSTOMER_NUMBER]),FILTER(ALL(REVENUE),REVENUE[GL_PERIOD_START_DT]<MIN('CALENDAR'[Date])))))


Lost Customer Count = COUNTROWS(EXCEPT(CALCULATETABLE(VALUES(REVENUE[CUSTOMER_NUMBER]),DATEADD('CALENDAR'[Date],-1,MONTH)),VALUES(REVENUE[CUSTOMER_NUMBER])))+0

     

     

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

     

    Best Regards,

    Dedmon Dai

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , You can try measures like

example measures

here sales measure will be sum(Table[USD_BUDGET_RATE])

 

MTD = calculate([Sales],datesmtd('Date'[Date]))
LMTD = calculate([Sales],DATESMTD(DATEADD('Date'[Date],-1,MONTH)))	
Lost Customer This Month = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([MTD]) && not(ISBLANK([LMTD])) , 1,BLANK()))
New Customer This Month = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([LMTD]) && not(ISBLANK([MTD])) ,1,BLANK()))
Retained Customer This Month = if(not(ISBLANK([MTD])) && not(ISBLANK([LMTD])) , 1,BLANK())

 

 

 

refer for more details

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529