Create Headcount Table based on Start and End Date

[dom-mitchell]

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:

Month	Headcount
Jan-2018	64
Feb-2018	64
Mar-2018	67
Apr-2018	66
May-2018	68

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!  

[dom-mitchell · Accepted solution]

I finally managed to find a solution and after multiple attempts at it not working (I had a tiny syntax error in my Dax), I managed to get the tips in this YouTube video to work for me:

Power BI Abstract Thesis Episode 65: Average of Rolling, Average of Snapshots - YouTube

Thanks to @amitchandak and the link over at Solved: calculate average headcount & attrition - Microsoft Power BI Community