Retention calculation based on lookup

[aar21292]

Hi all

I'm new to Power BI, and was wondering how to get an employee retention % calculation.

What I need is get a list of ids at start of period (min of date period used in X-axis) example year, month, week etc
Then get the another list of ids at the end of period selected above (example if using end of year, month, week etc) and lookup unique ids with the list of start of period and get a count of how many remained.
Then divide the count at the end of period with the total at the start of period.

So as an example if I use month in my X-axis, I want to count the number of employees at start in January (example 100), then get a count of how many employees stayed (from the starting 100) at the end of January (example 75) and divide the 75 by 100. 

What measures and calculations would I need to achieve this?

Thanks
Aaron

[lbendlin]

It woud be easier to record the leaving events, rather than having to confirm active status for all employees at the end of each period.

[lbendlin]

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[aar21292]

@lbendlin 
So a sample of the data would be something like this:

Created Date	Unique Id	Status
01/01/2023	12345	Active
01/01/2023	12346	Active
01/01/2023	12347	Active
03/01/2023	12348	Active

Created Date	Unique Id	Status
31/01/2023	12345	Active
31/01/2023	12346	Active
31/01/2023	12348	Active
22/02/2023	12349	Active

So let's say in my charts I have slicers to calculate retention for the period 01/01/2023 - 31/01/2023. So what I would like to do is count unique records that were created at start of period. Then count unique records at the end of the period that were also found at start of period. Then divide the end count by the start count.

Referring to the above data set, on 1st January we had 3 created records. One of those records left, and on 31st January we only remained with 2 records that were in the 1st January list. So my expected result should be 2/3=67% retention.