Count New Hire Per Month

[Anonymous]

Hi,

I have a table of Headcount that has below fileds and I need to count the new hired per month:

Table = Headcount

Fields = 

Report Effective Date (which is the monthly report date, that changes every month)

Employee ID

Hire Date

Any recommendations for creating the suitable measure?

[SpaceDad]

Hi,

I recently came across the same issue. Here is the solution I used in my report:

New Hires by Period = CALCULATE(COUNTROWS(<Data source>),

FILTER(VALUES([Hire Date]), [Hire Date] <= MAX(Dates[Date])),

FILTER(VALUES([Hire Date]), [Hire Date] >=MIN(Dates[Date])))

Let me know if it worked.

[zinah]

Hi,

Thanks for your suggestion.

I was trying to build the measure that you suggested but this part of the measure is not clear, what do you mean by MAX(Dates[Date])?

As I mentioned in my previous post, these are the fields I have:

Table = Headcount

Fields = 

Report Effective Date (which is the monthly report date, that changes every month)

Employee ID

Hire Date

[SpaceDad]

Would you like some help with creating a 'dates' table?

[zinah]

That would be great.

[SpaceDad]

Okay. First thing is to find out what the first hiring date is in your dataset. Once you know that, go to the "Modeling" tab, and click "New Table." For example, if the first date in the dataset for “Hire Date” is 09/05/2005 then that is the first date you will need to use.

In the formula bar you'll type in Dates = CALENDAR(“09/05/2005”, TODAY())

After you hit the enter key a dates table should be generated that ranges from the first date in the dataset to the current day date.

From here you will want to go to the “Model” view by selecting it on the left-hand side of the screen. Then create a relationship between the “Dates” table and “Hire Date” in the original dataset.

[SpaceDad]

Hi zinah,

The "Dates[Date]" is a reference to a table consisting of all the possible dates in the dataset. In order for the solution to work you will need to create a separate table from your data source. The first date in the "Dates" table should be the oldest date in your original data source. For example, the oldest hire date. Then you will want to use the “TODAY()” function, so that the current date is added to the date table as time progresses.
Once you have a “Dates” table created, you will want to create a relationship between “Hire Dates” and the dates in the “Date” table. After you have done all that, then the previously mentioned formula will work. You should be able to view head count for any given date using which ever visualization works best for your report.

[Anonymous]

To give more details, I have Report Date field and Hire Date field, and when I filtered the data using "Filter on this visual" and set below condition in order to get March new hires only. Is there any suggested measure to calulcate this instead of setting the dates manually?

Note: the report date is always on the end of each month, for example for March data, the report date is 3/31/2022

[Anonymous]

Hello,

Any suggestions please?

Thank you!

[amitchandak]

@Anonymous , the Expected output is not very clear

refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Hi, so what I'm trying to do is to calculate only the new hires each month from the employee active data. The data has employees and among those employees there are new hires, what I'm trying to accomplish is to count only the new hires on selected month. I tried below measure but the results were the total active count of emplyees not the new hires. Anything I could change in below measure to count only the new hires and not total?

New Hires =

VAR currentDate =

MAX (HR_Headcount_Feed[Hire_Date])

Return

CALCULATE(

DISTINCTCOUNT(HR_Headcount[Employee ID]),

FILTER(

HR_Headcount,

(HR_Headcount[Hire_Date] <= currentDate)

)

)

[v-kkf-msft]

Hi @Anonymous ,

Please try the following formula:

New Hires =
VAR currentDate =
MAX (HR_Headcount_Feed[Hire_Date])
Return
CALCULATE(
DISTINCTCOUNT(HR_Headcount[Employee ID]),
FILTER(
HR_Headcount,
HR_Headcount[Hire_Date] <= currentDate 
 && HR_Headcount[Hire_Date] >= MIN(HR_Headcount_Feed[Hire_Date])
)
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-kkf-msft 

Thank you for your help. I tried your suggested measure but it didn't work as the table still show the total active headcount for each month, instead only new hired for each month.

Not sure why it's not working, when I validate the the data in a pivot table, and filter by hire date, I can see the expected results. I also tried to use Fitlers on this visual and chose hire date by March 2022 to see the results it worked. But this is not an efficient way as I need a measure to do that, rather than me manually choosing the month every month I refresh the data. Is there any suggestion?

Thank you,

Zina