Employee Turnover

[JasonP]

First, thank you to this community! This is my first post but I have built an extensive report with almost no excel or technical background from your many posts. 

I have reviewed several questions similar to this but I have not been able to piece together a solution that works yet. 

I have a table called Agents that has a StartDate and TermDate column. The reports deal with multiple offices, sales types, sales dates, agents, and sale locations. I want to be able to show...... 

1. The sum and/or average of active associates within an office, select offices, or whole company at any time point depending on the office and time filter set. 

2. The turnover rate by month and year depending on the office or time filter set. 

I have the proper relationships between office and agent as well as with my sale tables. Just don't quite understand how to do what I want in a way that would give me what I want with any filters set. The hope is to not only be able to see basic active associates and turnover but draw correlations between sales and those numbers and perhaps leadership and those numbers. 

[v-sihou-msft · Accepted solution]

@JasonP

In this scenario, it looks you already have a fact sales table. You also have dimension tables like Agents, Office. To have the fact data sliced by time level, you need to have a date dimension table connecting fact table with date key column. And you should have a column in your fact table to tag if a deal is turnover. Then you can filter the "turnover" table context in your calculation.

Regards,

[dkay84_PowerBI]

First, you will likely need a date table as well.  Second, can you define how you calculate Employee Turnover (# leaving per month? net of new hires? etc.)