Graph of monthly returning, lost and new customers, and table of who they are and their sales

[ebeauchamp]

Hi, I've been working on attrition measures for a while now, but have not yet found a post or example that would achieve this on a same report page:
- A graph showing, for each month over a year, three bars:
   a) The number of returning customers, eg. who were purchasing last month and are this month too
   b) The number of new customers, eg. who were not purchasing last month, but started this month
   c) The number of lost customers, negative number, eg. who were purchasing last month but not this month

- On the same report page, clicking one of the above bars would filter another table that lists the customer IDs and their respective sales numbers (or lost sales), for the month corresponding to the selected bar.

In essence, it is similar to what is found here:
https://blog.enterprisedna.co/analyze-who-your-lost-customers-are-using-power-bi-dax/

But with the ability to analyze the returning and new customers, in addition to the lost ones. Does it seem like possible or too complex of a challenge? It seems like the dna example does have the ability to toggle between Lost and New customers, which already would be great, but since the demo does not cover it, I can't seem to figure out out how to do it.

Thanks in advance for your help!
Erik

[v-kelly-msft · Accepted solution]

Hi @ebeauchamp ，

I created a simple table as example:

First create a slice month table that you could dynamically see the changes between months:

slicer table = VALUES('Table'[Month])

Then create 6 measures as below；

Flag_The number of lost customers = 
var lastmonth=SELECTEDVALUE('slicer table'[Month])-1
var status_lastmonth=CALCULATE(SUM('Table'[is purchased]),FILTER(ALL('Table'),'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Month]=lastmonth))
var status_thismonth=CALCULATE(SUM('Table'[is purchased]),FILTER(ALL('Table'),'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Month]=SELECTEDVALUE('slicer table'[Month])))
Return
IF(status_lastmonth>0&&status_thismonth=0,1,0)

Flag_The number of new customers = 
var lastmonth=SELECTEDVALUE('slicer table'[Month])-1
var status_lastmonth=CALCULATE(SUM('Table'[is purchased]),FILTER(ALL('Table'),'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Month]=lastmonth))
var status_thismonth=CALCULATE(SUM('Table'[is purchased]),FILTER(ALL('Table'),'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Month]=SELECTEDVALUE('slicer table'[Month])))
Return
IF(status_lastmonth=0&&status_thismonth>0,1,0)

Flag_The number of returning customers = 
var lastmonth=SELECTEDVALUE('slicer table'[Month])-1
var status_lastmonth=CALCULATE(SUM('Table'[is purchased]),FILTER(ALL('Table'),'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Month]=lastmonth))
var status_thismonth=CALCULATE(SUM('Table'[is purchased]),FILTER(ALL('Table'),'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Month]=SELECTEDVALUE('slicer table'[Month])))
Return
IF(status_lastmonth>0&&status_thismonth>0,1,0)

The number of lost customers = SUMX(VALUES('Table'[Customer]),'Table'[Flag_The number of lost customers])

The number of new customers = SUMX(VALUES('Table'[Customer]),'Table'[Flag_The number of new customers])

The number of returning customers = SUMX(VALUES('Table'[Customer]),'Table'[Flag_The number of returning customers])

And you will see:

 For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mahoneypat]

If you can share your pbix (or a mock up of similar) through a link to Google Drive, OneDrive, etc., specific measures can be proposed.

Regards,

Pat

[mahoneypat]

This could all be done except the part about filtering on just one of the bars in the clustered column chart.  That click would select the YearMonth not just those customers.  However, you could use conditional formatting in the table below (or a disconnected table/slicer) to highlight new/lost/repeat.  You would just need to create a measure with two variable (current month and previous month sales) and then do AND(ISBLANK(... kind of logic to tell which type of customer they are and return the right label, number, and/or color.

Regards,

Pat

[ebeauchamp]

Thanks Pat. Any suggestions regarding the types of Lost/New/Returning measures that would work best to achieve this?