Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, I am trying to calculate my company's retention rate and haven't found a previous post yet that is calculated in the way I need to. I need to find the number of employees who began a time period and the number of those employees who were still active at the end of a time period. I have a data table with a hire and termination date, as well as a date table with an active relationship between the hire date and the date table and an inactive relationship between the termination date and date table. I am hoping to see this calculation by Quarter and End of Year views.
HR Analytics - Active Employee, Hire and Terminati... - Page 2 - Microsoft Power BI Community I have gone through this blog but believe the % change is a different calculation then what I am looking for but if there is a way to repurpose that to suite my needs let me know.
Good Day @jbres
I have assumed the following as the raw data,
Quarter year(Calculated column) =
Quarter Year = "Q"&QUARTER('Table'[Hire date])&" "&YEAR('Table'[Hire date])
In the same way, I have calculated the Master calendar
You need not have to add the relationship between the master calendar and raw data.
Add a slicer to the visualization pane and use the newly created Quarter column as the slicer value.
Create a measure and use the below formula.
Retention rate =
Var newjoiners = CALCULATE(COUNTROWS('Table'),'Table'[Quarter Year]=SELECTEDVALUE('maste Calendar'[Quarter Year]))
Var Tenured = CALCULATE(COUNTROWS('Table'),'Table'[Quarter Year]<SELECTEDVALUE('maste Calendar'[Quarter Year]))
Var result =DIVIDE(newjoiners,Tenured,blank())
Return result
add the measure to a KPI Card.
I hope this will work as you intended.
If this is the solution please mark this reply as a solution.
Regards,
Atma.
Hello, sorry for the delayed response, I was out of town for a couple of weeks. I created the columns and measure you provided but it doesn't generate any data, even when I add the quarter year column as a filter.
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |