Forum Discussion
How To Calculate Annual Retention Rate Month-Over-Month
Hello All,
I've hit a bit of a roadblock with this one, and I'm hoping the community can help me resolve it. I'm still a fairly new PBI user, so it's likely I'm missing something obvious.
The goal: to calculate an annual retention rate trending month-over-month.
Retention Calculation: (Headcount at end of reporting period) / (Headcount at beginning of reporting period); New Hires are NOT counted in this calculation. We want to use the headcount at the beginning of the year (as of 1.1.24) and the ending headcounts at the end of every month (1.31, 2.28, 3.31).
I was able to create a formula that shows me the monthly retention rates, but it's only based on data for that particular month. I'm attaching a PBIX with sample data, but to give an idea of what we're looking for, here are some example numbers:
BOY Headcount: 2500
January: Ending Headcount of 2480
February: Ending Headcount of 2466
March: Ending Headcount of 2440
My calculation is using the beginning and ending headcounts of each month, rather than using the BOY Headcount I want to use, so it looks like this:
| Month | Beginning HC | Ending HC | Retention Rate |
January | 2500 | 2480 | 99.2% |
| February | 2480 | 2466 | 99.4% |
| March | 2466 | 2440 | 98.9% |
However, that's not what we want. What we want to see is this:
| Month | Beginning HC | Ending HC | Retention Rate |
January | 2500 | 2480 | 99.2% |
| February | 2500 | 2466 | 98.6% |
| March | 2500 | 2440 | 97.6% |
What should be happening is the retention rate either stays the same or goes down - it should never go up. We're only calculating based on the number of people with the organization at the beginning of the year, and we're not counting the people (new hires) who join the organization during the year.
You'll see in my PBIX file that I was able to calculate the BOY HC, but my EOY and Retention formulas only work on January and are not working in Feb or March. Any thoughts on how to fix this?
Please let me know if additional information is needed. Thanks!
Hi Daniel,
Here's my updated pbix file.
This is what the table looks like now:
The key changes I made were:
- Creating a relationship between the calendar table and the All Data table
- Replacing the month field in your table visual with the month field from the Calendar table
- Slight tweaks to your measure
Since you said you are fairly new, I love recommending SQLBI's free courses whenever I can. They'll help set you up with a good foundation! 😄
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)P.S. Need a more in-depth consultation for your Power BI issue? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
3 Replies
Wilson_Memorable Member
Hi Daniel,
Here's my updated pbix file.
This is what the table looks like now:
The key changes I made were:
- Creating a relationship between the calendar table and the All Data table
- Replacing the month field in your table visual with the month field from the Calendar table
- Slight tweaks to your measure
Since you said you are fairly new, I love recommending SQLBI's free courses whenever I can. They'll help set you up with a good foundation! 😄
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)P.S. Need a more in-depth consultation for your Power BI issue? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
- Ashish_Mathur
Super User
Hi,
PBI file attached.
Hope this helps.
