Employee Count on any given date with start and end dates

[justincordasco]

I've seen mulitiple threads, but nothing I do works. I can only get values return based on start dates. For instance, employees who started in 2015 do not show if the date range is 2016 -2017, even though they were employed. 

[Icey · Accepted solution]

Hi @justincordasco ,

Is this problem sloved?

If it is sloved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[Icey]

Hi @justincordasco ,

Is this problem sloved?

If it is sloved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[justincordasco]

Hi, 

That will work. Not exactly what I'm looking for, but could be get the job done. However, I'm not having any values populate. What am I doing wrong? 

[Icey]

Hi @justincordasco ,

Are you creating a column or a measure? You should create a measure.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Icey]

Hi @justincordasco ,

Is the screenshot below what you want?

If so, you can create your measures like so:

2016-2017 = 
VAR StartYear =
    YEAR ( MAX ( 'Table'[Start Date] ) )
VAR EndYear =
    IF (
        ISBLANK ( MAX ( 'Table'[End Date] ) ),
        YEAR ( TODAY () ),
        YEAR ( MAX ( 'Table'[End Date] ) )
    )
RETURN
    IF ( StartYear <= 2017 && EndYear >= 2016, 1, 0 )

Count 2016-2017 = SUMX ( 'Table', [2016-2017] )

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

You should try providing us a little more details...

[justincordasco]

I'm trying to correlate head count to things like sales, expeneses, etc. So I need to know on any given date how many employees were active. Can I do this by using only the employee start and end date? I've seen where other people have accomplished this but I cannot get it to work. I can only get a return that is based off of the start date. 

[Anonymous]

Again.

This is really not enough info.

Have you created a date dimension? Is it connected to the Start Date. Of course it works only with start date. 

Do you need to have a *measure* or calculated column? One way is to store it in a precalculated table that has one column for the date and the other is something along the lines of

CountOfEmployees=
VAR thisDay=ThisTableName[NameOfColumnWithDate]
RETURN
COUNTROWS(FILTER(Employee;Employee[StartDate]<=thisDay && Employee[EndDate]>=thisDay))

However if you need a measure a picture or screenshot of your data model is necessary