Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

Count New Hire Per Month

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?

12 REPLIES 12
SpaceDad
Frequent Visitor

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.

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

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

That would be great.

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.

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
Not applicable

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

 

zina_t_0-1650305545441.png

 

 

Anonymous
Not applicable

Hello,

 

Any suggestions please?

 

Thank you!

amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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)
)
)
zina_t_0-1648733739960.png

 

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
Not applicable

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

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.