cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
always_joy
New Member

How to count active employees per month by hired and term date

Hello!

 

I have a table that provides employee ID, their hired date and term date. By using hired and term date, i need to display how many team members were active per month. 

 

For example, row 2-4 will be considered as active employees as of January 2021 and row 5-8 will be considered as active employees as of Feb 2021. I am unsure I should create this into dax. Appreciate your help! 

 

always_joy_1-1657763584299.png

 

4 REPLIES 4
Saap
Resolver II
Resolver II

Hi @always_joy 

1. Disconnect your dates table from fact table.
2. Use this measure:

 

ACTIVE =
CALCULATE( DISTINCTCOUNT( TABLE[Employee ID] ),
FILTER( VALUES( TABLE[Hired Date] ), TABLE[Hired Date] <= MAX( CALENDAR[Date] ) ),
FILTER( VALUES( TABLE[Term Date] ), OR( TABLE[Term Date] >= MIN( CALENDAR[Date] ), ISBLANK( TABLE[Term Date] ) ) ) )

3. Put this measure into a matrix and set visual level filter to be equal or greater than 1.

Eyelyn9
Community Support
Community Support

Hi @always_joy ,

 

According to your sample data, for Jan-21, the count of Active Hires seems to be 6 not 3.

Eyelyn9_0-1658124454517.png

In addition, not very clear about this:

If an employee has future term date for the current month, i still want to be able to count that employee

but the current dax is removing all employees with the term date for the current month. How can I update the below dax to capture that?

 


So could you please provide me with more details about your table and your problem to help us clarify your scenario? Or share me with your pbix file after removing sensitive data.

Refer to:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

 

 

Best Regards,
Eyelyn Qin

amitchandak
Super User
Super User

@always_joy , refer m blog on same topic

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

 

Or the attached file after signature



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Thank you @amitchandak. A quick question regarding to current employees. 

 

If an employee has future term date for the current month, i still want to be able to count that employee

but the current dax is removing all employees with the term date for the current month. How can I update the below dax to capture that? Thank you!

 

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))
 
always_joy_0-1657817888018.png

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors