Data Slicer for Active Employees

[Gewoodruff]

Hello,

I have a list of employees, some active and some terminated. I have created a measure to show me which employees are active. 

What I would like to do now is be able to use a date slicer so you can see how many active employees there were at any period. However when i use the date slicer now I only get a count of employees whos start dates are in that period. 

My date table is currently based on the employee start date and I think this might be where the problem is coming in. I only have two dates relating to my employees which is start date and termination date. 

The below is my measure for active employees: 

var CurrentDay = TODAY()

return

CALCULATE(DISTINCTCOUNT('All data'[EmployeeID]), FILTER('All data','All data'[EmploymentStartDate] <= CurrentDay && isblank ('All data'[TerminationDate])|| ('All data'[EmploymentStartDate] <= CurrentDay && 'All data'[TerminationDate] >= CurrentDay || ('All data'[EmploymentStartDate] <= CurrentDay && 'All data'[TerminationDate] >= CurrentDay && 'All data'[EmploymentStartDate]> 'All data'[TerminationDate]) + 0)))

Any ideas how i can make this work?

Thanks so much!

Georgia

[v-robertq-msft]

Hi, @Gewoodruff 

Have Jihwan_Kim’s reply helped you to find the solution to this problem?

To deal with the blank value in the [termination dates] column, you can also try to use the “replace values” function in the Power quey to replace the blank with “9999-12-31” to make sure it won’t affect the value of the measure.

https://eriksvensen.wordpress.com/2018/05/03/powerquery-replace-values-in-one-column-with-the-value-...

If you have solved this problem, would you like to mark Jihwan_Kim’s reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[Jihwan_Kim · Accepted solution]

Hi, @Gewoodruff 

Please check the below picture and the sample pbix file, whether it is what you are looking for.

I tried to create a sample pbix file based on the explanation.

I prefer to have inactive relationships like below if a fact table has more than two date-related columns.

Active Employees Count =

CALCULATE (

COUNTROWS ( Employees ),

FILTER (

Employees,

Employees[Startdate] <= MAX ( Dates[Date] )

&& Employees[TerminationDate] >= MIN ( Dates[Date] )

)

)

https://www.dropbox.com/s/8wwi5qi9ckiq6t6/gewoodruff.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Gewoodruff]

Hi Jihwan,

Your example works well however I think it might not be working for because i have some blank termination dates. do you have any advice around this?

Thanks,

Georgia

[Jihwan_Kim]

Hi, @Gewoodruff 

Thank you for your feedback.

In case the termination date is blank, please check the below-amended measure.

Sometimes, I saw that some organizations do not leave it blank, but insert the far far away date, for instance, 2999-Dec-31.

Active Employees Count =

CALCULATE (

COUNTROWS ( Employees ),

FILTER (

Employees,

Employees[Startdate] <= MAX ( Dates[Date] )

&& OR (

Employees[TerminationDate] >= MIN ( Dates[Date] ),

ISBLANK ( Employees[TerminationDate] )

)

)

)

https://www.dropbox.com/s/8wwi5qi9ckiq6t6/gewoodruff.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[amitchandak]

@Gewoodruff , Try like

var CurrentDay = TODAY()
return
CALCULATE(DISTINCTCOUNT('All data'[EmployeeID]), FILTER('All data','All data'[EmploymentStartDate] >= CurrentDay && ( isblank ('All data'[TerminationDate]) || 'All data'[TerminationDate] <= CurrentDay )))

Also check

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...