cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aneeshvarghese Frequent Visitor
Frequent Visitor

Customer retention new vs old Customers

 

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

 

 

Capture.PNG

 

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
balaganeshv2201 Frequent Visitor
Frequent Visitor

Re: Customer retention new vs old Customers

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 Frequent Visitor
Frequent Visitor

Re: Customer retention new vs old Customers

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??

aneeshvarghese Frequent Visitor
Frequent Visitor

Re: Customer retention new vs old Customers

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

15 REPLIES 15
luxpbi Member
Member

Re: Customer retention new vs old Customers

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!

balaganeshv2201 Frequent Visitor
Frequent Visitor

Re: Customer retention new vs old Customers

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 Frequent Visitor
Frequent Visitor

Re: Customer retention new vs old Customers

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??

Super User
Super User

Re: Customer retention new vs old Customers

Hi,

 

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

aneeshvarghese Frequent Visitor
Frequent Visitor

Re: Customer retention new vs old Customers

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

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

aneeshvarghese Frequent Visitor
Frequent Visitor

Re: Customer retention new vs old Customers

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

Super User
Super User

Re: Customer retention new vs old Customers

Hi,

 

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

 

Hope this helps.

 

Untitled.png

aneeshvarghese Frequent Visitor
Frequent Visitor

Re: Customer retention new vs old Customers

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 Frequent Visitor
Frequent Visitor

Re: Customer retention new vs old Customers

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]) ) )