Calculating a monthly employee count from a start and end date range

[joshcomputer1]

Hi there, 

I just need some help organizing how to do this.  I have a table that displays the employees' names, title, start date, end date and "active?".  The active column just shows whether they are an active employee today or not.  

What I am wanting is to be able to show (based on the start date and end date columns how many employees we had for each month.  The range right now only goes back to Jan. 1, but want to get started on this. Any help is appreciated.

**I thought about just having a column for each month with a 1 or 0 if they were active during the month.  This would work, except there is additional columns added for each month and this is not maintenance free (at least not how I had it).  I am familiar with excel but new to power BI. 

table= tblEmployees

column= EmpName

column= colStartDate

column= colEndDate

column= colActive?

[GilbertQ]

Hi @joshcomputer1

Do you have some sample data, which will make it easier?

You could so a measure with the following syntax below, but you would require to have a Date Table in order for this to work. Where you create a relationship your data to the Date table.

radacad.com/do-you-need-a-date-dimension

Total Employees = 
VAR EarliestDate = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED()) 
RETURN CALCULATE(DISTINCTCOUNT('tblEmployees'[colActive]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date]) && 'Calendar'[Date] >= EarliestDate))

[joshcomputer1]

How do I add a relationship to the date calendar? Relate the start and end dates columns to it? I already have months and years and week numbers columns in most of my excel docs and would like to move away from that.

[v-ljerr-msft]

Hi @joshcomputer1,

The relationship to the date calendar is not needed here.

Could you share a sample pbix file, so that we can better assist on this issue? You can upload it to OneDrive or Dropbox and post the link here, or sent it to me in private message. Do mask sensitive data before uploading. 

Regards

[joshcomputer1]

Team	Name	GM	SM	Role	Start Date	End Date	Active?
First	Michael	Kayes	Bob	IT Support Agent	3/30/2016	5/25/2017	1
First	Enrique	Kayes	Bob	IT Support Agent	12/23/2015	5/25/2017	1
First	Jerry	Kayes	Bob	IT Support Agent	10/21/2013	5/25/2017	1
First	Jim	Kayes	Bob	SM	7/1/2012	5/25/2017	1