Forum Discussion
Data Slicer for Active Employees
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:
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] )))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
5 Replies
- Jihwan_Kim
Super User
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] )))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
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] ))))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
- v-robertq-msft
Community Support
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.
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.
