Customer retention new vs old Customers

[aneeshvarghese]

I want to calculate yearly retension report for the follwing data.

1. I like to find how many old customers from 2017 are with us in 2018
2. Also number of new customers in 2018.

Plz help. I am a newbie and need to generate this report for the management.

Also plz consider having more than one instances for customers in the same year. 

For pbi file Click here

Found the solution 
Check this out
Link

[balaganeshv2201 · Accepted solution]

I want to calculate yearly retension report for the follwing data.

1. I like to find how many old customers from 2017 are with us in 2018
2. Also number of new customers in 2018.

Plz help. I am a newbie and need to generate this report for the management.

I like to find how many old customers from 2017 are with us in 2018

 Measure =
COUNTROWS(INTERSECT(SELECTCOLUMNS(FILTER(table1,year(Table1[year])=2017),"2017_yr_nm",Table1[name]),
SELECTCOLUMNS(FILTER(table1,year(Table1[year])=2018),"2018_yr_nm",Table1[name])))
Also number of new customers in 2018.
 Measure = 
COUNTROWS(except(SELECTCOLUMNS(FILTER(table1,year(Table1[year])=2017),"2017_yr_nm",Table1[name]), 
SELECTCOLUMNS(FILTER(table1,year(Table1[year])=2018),"2018_yr_nm",Table1[name]))) 
 

[aneeshvarghese · Accepted solution]

Hi function to find Retension of customers from last year is working perfectly. But 2nd equation for finding new customers is returning a wrong value. Can you plz look into this for me??

[balaganeshv2201 · Accepted solution]

I want to calculate yearly retension report for the follwing data.

1. I like to find how many old customers from 2017 are with us in 2018
2. Also number of new customers in 2018.

Plz help. I am a newbie and need to generate this report for the management.

I like to find how many old customers from 2017 are with us in 2018

 Measure =
COUNTROWS(INTERSECT(SELECTCOLUMNS(FILTER(table1,year(Table1[year])=2017),"2017_yr_nm",Table1[name]),
SELECTCOLUMNS(FILTER(table1,year(Table1[year])=2018),"2018_yr_nm",Table1[name])))
Also number of new customers in 2018.
 Measure = 
COUNTROWS(except(SELECTCOLUMNS(FILTER(table1,year(Table1[year])=2017),"2017_yr_nm",Table1[name]), 
SELECTCOLUMNS(FILTER(table1,year(Table1[year])=2018),"2018_yr_nm",Table1[name]))) 
 

[aneeshvarghese · Accepted solution]

Hi function to find Retension of customers from last year is working perfectly. But 2nd equation for finding new customers is returning a wrong value. Can you plz look into this for me??

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[aneeshvarghese]

https://1drv.ms/u/s!Anz-04Yz_4hzb6z9M5wV3QjV6w8

Here is the link for the file. Open it in a new tab

[aneeshvarghese]

Also plz consider having more than one instances for customers in the same year. 

[Ashish_Mathur]

Hi,

For 2018, the answer should be 11.  You may download my PBI file from here.

Hope this helps.

[aneeshvarghese]

thanks mate, i am facing a bit hiccups finding from where it is pulling year 2018. As you look into my Pbi file you can see that i can toggle btw diffreent year to find new vs old customers for that year. If you could push a slicer also into you file to toggle btw diffreenet years that would be really nice. Thanks

[balaganeshv2201]

hi bro  try this  i added this measure in to your pbix file and its pulling 18 as the answer :

18 new customers

Measure = COUNTROWS(except ( SELECTCOLUMNS(Table2,"all_yr_nm",Table2[name]), SELECTCOLUMNS(FILTER(table2,year(Table2[year])=2018),"2018_yr_nm",Table2[name]) ) )

[luxpbi]

Hi @aneeshvarghese,

You have marked the answer of @balaganeshv2201 as accepted, I'm not sure that this is the answer you needed. 

Please, if you have solved your needs, mark another answer as accepted. 

This will help others to find the correct solution in less time. 

Thanks in advance. 

[aneeshvarghese]

The solution has two parts first part gives me the answer while second part gives wrong answer. i am new i am not sure how exactly this works. if you could give some advise?

[aneeshvarghese · Accepted solution]

I think i found the solution. Hope it is useful to everyone. I have created a parameter for year which you can swap for months or years based on adding a calender to solution.

Here is the link.

Check this

[aneeshvarghese]

Total count of neww customer is 7. You can check that from the report table.

[Ashish_Mathur]

Hi,

To verify whether the new customer count in 2018 is 11 or 7, i will need the MS Excel file.  Share the data in an Excel file format.

[aneeshvarghese]

SOrry i couldnt share it before Here is the link

[luxpbi]

Hello @aneeshvarghese,

Here is a great article by the best, Marco and Alberto.

New and Returning Customers

This should be enought for you.

Best Regards!