New + Lost Customer Analysis (based on Recurring Revenue)

[Anonymous]

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 customers appear every month).

I want to identify every month how many customers stop generating revenue and how many customers begin generating revenue so I can plot as a stacked column chart.

(i.e. Customer X had revenue last month but not this month = lost customer. Customer Y had no revenue last month but has revenue this month = new customer).

I also need to show a Top 10/Bottom 10   New/Lost Customers by Revenue won/lost each month

SAMPLE DATA:   https://www.dropbox.com/s/71o28dww2xq3bkl/Test.pbix?dl=0 

Expected Results:

GL_PERIOD_START_DATE	CUSTOMER_NUMBER	USD_BUDGET_RATE
Jan-21	AAA	$100
Jan-21	BBB	$500
Feb-21	AAA	$100
Feb-21	BBB	$500
Mar-21	AAA	$100
Mar-21	BBB	$500
Apr-21	BBB	$500
May-21	BBB	$500
May-21	CCC	$300
Jun-21	BBB	$500
Jun-21	CCC	$300

In the above example, expected results would be:

Jan-21: 0 new, 0 lost
Feb-21: 0 new, 0 lost
Mar-21: 0 new, 0 lost
Apr-21: 0 new, 1 lost
May-21: 1 new, 0 lost


Thanks!

[v-deddai1-msft · Accepted solution]

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]

@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