Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])))
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??
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
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])))
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??
Hi,
Share the link from where i can download your PBI file.
https://1drv.ms/u/s!Anz-04Yz_4hzb6z9M5wV3QjV6w8
Here is the link for the file. Open it in a new tab
Also plz consider having more than one instances for customers in the same year.
Hi,
For 2018, the answer should be 11. You may download my PBI file from here.
Hope this helps.
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
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]) ) )
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.
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?
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
Total count of neww customer is 7. You can check that from the report table.
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.
Hello @aneeshvarghese,
Here is a great article by the best, Marco and Alberto.
This should be enought for you.
Best Regards!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |