How To Calculate Annual Retention Rate Month-Over-Month

[Anonymous]

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!

PBIX Example File 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Wilson_ · Accepted solution]

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.

[Anonymous]

This worked perfectly - thank you for your help!