Forum Discussion
Retention calculation based on lookup
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
3 Replies
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-p/963216
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/1447523lbendlin
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.
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.
