Calculating Month Over Month Retention/Attrition Based on Start/Term Dates

[Anonymous]

Hi all!  Hoping to get some guidance on modeling HR statistics based on raw data we have from our HR team.

I have a raw data file of all employees in our company, along with their start date and term date (if applicable).  Example is attached for reference.

What I'm trying to do is build a matrix where we can see an average monthly attrition/retention rate by year, then drill into the details by department.  Ideally, I'd also like to be able to model seasonality by comparing trends from the same time period in previous years.

Here are the columns in my source file:

Personnel Num

Employee Name

Employee Department

Direct Manager Name

Empl Status

Start Date

Term Date

I have three formulas that I'm using to calculate retention now:

MonthStart_ActiveEmployee = 
CALCULATE(
COUNT(TerminationTrendsData[Personnel Num]),
FILTER(TerminationTrendsData, TerminationTrendsData[Start Date]<(FIRSTDATE('Date'[Date]))),
FILTER(TerminationTrendsData, TerminationTrendsData[Term Date]>=(FIRSTDATE('Date'[Date])) || TerminationTrendsData[Term Date] = BLANK())
)



TermedinMonth = 
CALCULATE(
COUNT(TerminationTrendsData[Personnel Num]),
FILTER(TerminationTrendsData, TerminationTrendsData[Term Date]>=MIN('Date'[Date])),
FILTER(TerminationTrendsData, TerminationTrendsData[Term Date]<=MAX('Date'[Date]))
)



Retention = 
Var Retained = [MonthStart_ActiveEmployee]-[TermedinMonth]
Var MonthRetention = Retained/[MonthStart_ActiveEmployee]
Return
MonthRetention



AttritionRate = if(
DATEDIFF(MIN('Date'[Date]), MAX('Date'[Date]), DAY)<=31, 
(([TermedinMonth]/[MonthStart_ActiveEmployee])), 
IF(DATEDIFF(MIN('Date'[Date]), MAX('Date'[Date]), MONTH)>=2, 
(([TermedinMonth]/[MonthStart_ActiveEmployee])/DATEDIFF(Min('Date'[Date]), MAX('Date'[Date]), MONTH)), [TermedinMonth]/[MonthStart_ActiveEmployee])

)

The attrition formula reflects a rough attempt at trying to add averaging into the equation.

All of these measures are accurate at a monthly level, but at a yearly level they get thrown off.

Any guidance/best practices on this?  

[v-shex-msft]

Hi @Anonymous ,

When you measure calculate on year level, it row contents is whole year, so the first one condition always not triggered.

For you scenario, I'd like to suggest you add conditions to check current row content level.(add a variable table to manually summarize table(year, month and category fields) records and calculate correspond month result)

After above steps, you can use iterator functions to summarize this to get year level result.

In addition, you can also take a look at following blog about measure calculate on total level:

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng

[Anonymous]

Any update to my question?  I reviewed this info but it doesn't appear to get at the crux of my issue, which is building a matrix that will accurately reflect these percentages both monthly and annually.

[v-shex-msft]

HI @Anonymous ,

I'd like some sample data for test and coding formula, you can upload it to onedrive or google drive then share link here.
Notice: do mask on sensitive data.

Regards,

Xioaxin Sheng

[Anonymous]

I'm not able to provide that data, but I have been combing through forums for similar examples, as I would imagine this is a relatively common use case.

The closest I've found is this item - https://community.powerbi.com/t5/Desktop/Can-I-filter-two-values-based-on-slicer-selection/td-p/3053...

I think a key issue I'm encountering is that my data has multiple date columns, so I can't connect either directly to the date table.  I've been trying to use USERRELATIONSHIP as a workaround, but that has been unsuccesful.

This example (https://docs.microsoft.com/en-us/power-bi/sample-human-resources) is the closest thing I've found to the end goal I'm seeking, but their data table has multiple lines for each employee which allows them to tie that column to the date table, and my data has a single line for each employee.

This seems relatively straightforward - I need to be able to calculate general HR and headcount trends based on a data table that includes a unique line for each employee with a column for their Hire and Term dates.  The fact that this has been so difficult to model within PowerBI has been really frustrating.

[Anonymous]

Should I just give up on help here?

[Anonymous]

Thanks Xiaoxin!  

I'm still ramping up with Power BI and I'm not sure what you mean by "add a variable table to manually summarize table" and it looks like the "Measure Totals" post is centered around measure creation, not variable tables.  

Could you direct me to more info about variable table creation?