cancel
Showing results for
Did you mean:
Highlighted
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.

Found the solution
Check this out

3 ACCEPTED SOLUTIONS

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

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

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.

Check this

15 REPLIES 15
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!

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

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

Hi,

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

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

Hi,

Hope this helps.

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

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