cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tWdex
Frequent Visitor

Count the number of employees between two dates

Hi, I need to create a measure to count the number of the employees per month/year so I can show it on line chart etc

 

Here  the table:

factory name *unique key*datehiredterm date
1john01/01/202101/02/202031/12/2999
1john01/01/202101/02/202031/12/2999
1john02/01/202101/02/202031/12/2999
1john02/01/202101/02/202031/12/2999
1john01/04/202101/02/202031/12/2999
1john01/04/202101/02/202031/12/2999
2willy01/01/202101/02/202002/02/2020
2willy01/01/202101/02/202002/02/2020
1jack 01/02/199902/01/2012

(jack is an one of the user than was hired and then terminated before the currently clocking system)

 

I dont think I can use "date" because there can be times when even if an employee is currently hired but there are no rows with his name for months(dont ask me why...), so a normal visualization with date on an axis and the count of distinct name as the other axis will lead to wrong values

 

I already created a AllStaff measure that calculate all staff ever employed 

DISTINCTCOUNT(name),ALLEXCEPT('table',factory))

This count all the employees we ever had

 

Aslo I would like the option to count the number of employees at the end of the month (30/31), meaning if you are watching the employees of feb 2012 jack would not be counted

 

If anyone can help me, thanks!

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi  @tWdex ,

 

First create a calendar table based on your date period ,then create a month column in the calendar table;

Finally create a measure as below:

Measure = 
VAR _month=SELECTEDVALUE('Calendar table'[Month])
var _maxdate=CALCULATE(MAX('Calendar table'[Date]),FILTER(ALL('Calendar table'),'Calendar table'[Month]=_month))
Return
CALCULATE(DISTINCTCOUNT('Table'[ name *unique key*]),FILTER(ALL('Table'),'Table'[factory]=MAX('Table'[factory])&&'Table'[term date]>=_maxdate&&'Table'[hired]<=_maxdate))+0

And you will see:

v-kelly-msft_0-1623657132698.pngv-kelly-msft_1-1623657146213.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

Hi  @tWdex ,

 

First create a calendar table based on your date period ,then create a month column in the calendar table;

Finally create a measure as below:

Measure = 
VAR _month=SELECTEDVALUE('Calendar table'[Month])
var _maxdate=CALCULATE(MAX('Calendar table'[Date]),FILTER(ALL('Calendar table'),'Calendar table'[Month]=_month))
Return
CALCULATE(DISTINCTCOUNT('Table'[ name *unique key*]),FILTER(ALL('Table'),'Table'[factory]=MAX('Table'[factory])&&'Table'[term date]>=_maxdate&&'Table'[hired]<=_maxdate))+0

And you will see:

v-kelly-msft_0-1623657132698.pngv-kelly-msft_1-1623657146213.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

View solution in original post

amitchandak
Super User IV
Super User IV

@tWdex , refer if my blog on HR can help

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors