Forum Discussion
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 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!
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])))+0If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
2 Replies
v-deddai1-msftCommunity Support
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])))+0If 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
Super User
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
