Forum Discussion

dom-mitchell's avatar
dom-mitchell
Frequent Visitor
3 years ago
Solved

Create Headcount Table based on Start and End Date

Hi there, I'm struggling with a key element of a report for my HR group. 

 

I have a data set ('Attrition Report') which contains two key columns, [Start Date] and [End Date].

I order to create some key measures, I need to use that data to create a table that can show me a month by month headcount number. I believe once I have that I can use the DATEDIFF functon to work out a rolling 12 month average. 

I'm stuck at the new table creation stage however; I can't even think how to get started on it!

I'm guessing I need to perform some sort of COUNT function but I'm probably way off!

By desired output would be a table that looks something like this:

MonthHeadcount
Jan-201864
Feb-201864
Mar-201867
Apr-201866
May-201868

etc....

 

I know I need the calculated column to look at 'Attrition Data'[Start Date] and count if it is before the month and ignore if there is an [End Date] also before the month.

The [End Date] field is blank is someone is still employed. 

Any help on this would be much apprecaited!