Count the number of employees between two dates

[tWdex]

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*	date	hired	term date
1	john	01/01/2021	01/02/2020	31/12/2999
1	john	01/01/2021	01/02/2020	31/12/2999
1	john	02/01/2021	01/02/2020	31/12/2999
1	john	02/01/2021	01/02/2020	31/12/2999
1	john	01/04/2021	01/02/2020	31/12/2999
1	john	01/04/2021	01/02/2020	31/12/2999
2	willy	01/01/2021	01/02/2020	02/02/2020
2	willy	01/01/2021	01/02/2020	02/02/2020
1	jack		01/02/1999	02/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!

[v-kelly-msft · Accepted solution]

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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[amitchandak]

@tWdex , refer if my blog on HR can help

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