cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
suegee
Regular Visitor

Employee Count based on Hire/Rehire date

Hi,  I am new to Power BI and tried to find something that was similar to what I was trying to do.  I didn't have much luck on this and appreciate any assistance.  I have data that has unique employee IDs, Hire/Rehire date, and other information.  But I am trying to get a count of employee IDs based on the Hire/Rehire dates.  

 

  • I want to get an employee count for each month in 2018.  For example, Jan 31, 2018 needs to include hire/rehires from this date all the way back to the first higher.  Then Feb 28, 2018 would include all of Feb all the way back to the first higher.
  • I want get an employee count for each month in 2017.  For example, Jan 31,2017 would include hire/rehires from this date back to the first hire and so on.
  • I created a date Calendar that just has the dates I want to use Jan 1, 2017 to Dec 31, 2018
  • I want  to chart the 2018 and 2017 data.
  • then add Employees YOY variance and the Employees YOY % change.

I can't seem to figure out how to separate the two years of data from my current active employee list.   I don't know how to post the sample data that I have.  So I have cut and pasted a copy of it.

 

IDhire/rehire
1383/26/2018
2103/7/2018
0901/15/2018
0911/15/2018
1081/15/2018
1371/15/2018
2631/8/2018
1361/3/2018
20912/27/2017
10712/18/2017
10012/11/2017
13512/4/2017
15712/4/2017
20810/16/2017
08910/2/2017
0769/25/2017
1739/25/2017
0389/18/2017
2078/21/2017
2528/14/2017
2008/3/2017
2067/31/2017
0887/10/2017
1727/10/2017
1515/15/2017
0215/1/2017
1565/1/2017
2053/27/2017
2033/20/2017
2043/20/2017
1472/1/2017
0991/18/2017
1501/9/2017
24412/5/2016
26211/28/2016
08710/31/2016
24910/11/2016
08610/3/2016
14310/3/2016
20210/3/2016
0859/12/2016
2339/12/2016
0208/22/2016
0848/15/2016
2618/15/2016
1347/5/2016
0985/30/2016
2485/16/2016
1465/2/2016
2474/25/2016
0833/29/2016
2013/28/2016
0192/29/2016
0822/29/2016
0812/16/2016
0372/8/2016
1451/20/2016
1491/20/2016
13311/23/2015
30011/16/2015
0809/21/2015
0189/1/2015
1548/24/2015
1998/18/2015
2928/17/2015
1488/10/2015
0786/29/2015
0796/29/2015
2466/8/2015
1326/3/2015
0775/25/2015
1715/4/2015
0974/13/2015
2994/13/2015
0173/30/2015
1313/30/2015
1063/23/2015
2972/17/2015
0531/12/2015
1301/6/2015
0161/5/2015
0751/5/2015
15312/15/2014
 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft v-cherch-msft
Microsoft

Re: Employee Count based on Hire/Rehire date

Hi @suegee

 

You may refer to below measure. 

Measure  =
VAR currentDate =
    MAX ( Data[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( EE_List ),
        FILTER ( EE_List, EE_List[Year] = 2017 && EE_List[Date] <= currentDate )
    )

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User IV
Super User IV

Re: Employee Count based on Hire/Rehire date

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Re: Employee Count based on Hire/Rehire date

Hi - Can you check this PBIX file is of any help:

https://files.fm/u/x85mwmey

 

Also, when I copy/pasted your date values, I got an error and I had to make some transformations to get them right. Please check if months and dates have been reversed for single digits. Else you can simply replace the entire date values with the correct ones.

suegee
Regular Visitor

Re: Employee Count based on Hire/Rehire date

Thanks Nikhil,  How would I write a measure that would only pick up the lastest number for each month in 2018 and a separate measure for 2017?  I see that you have an employee count that shows for each date, but I only want the latest number for that month.  Sorry, I am still learning how to write formulas here. 

 

I had used this formula based on another post and filtered by year, but I couldn't figure out how to get 2017 and 2018 separate from each other, so that I could write a measure for each in order to calculate the Year Over Year variance. 

2018 Active EE = 
VAR currentDate =
    MAX ( 'Date'[Date])
RETURN
    CALCULATE (
        COUNTROWS ( 'EE_List' ),
        FILTER (
            'EE_List',
            ( 'EE_List'[Hire_Date] <= currentDate)))

Thanks for helping me with this.

 

Microsoft v-cherch-msft
Microsoft

Re: Employee Count based on Hire/Rehire date

Hi @suegee

 

You may refer to below measure. 

Measure  =
VAR currentDate =
    MAX ( Data[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( EE_List ),
        FILTER ( EE_List, EE_List[Year] = 2017 && EE_List[Date] <= currentDate )
    )

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User IV
Super User IV

Re: Employee Count based on Hire/Rehire date

@suegee you need to add calendar dimension in your model and that it become very easy. There are many post on how to calendar in your data model.

 

Once calendar dimension is added, you will able to use year from that table to get total of each year.

 

Let me know if you need further assistance.






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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Super User IV
Super User IV

Re: Employee Count based on Hire/Rehire date

Hi,

 

What do you mean by "all the way back to the first higher"?  Do you mean to start counting from the very first date in your dataset?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
suegee
Regular Visitor

Re: Employee Count based on Hire/Rehire date

Hi Ashish_Mathur,

 

That's correct. Count the datasets from the first date. But keeping in mind that I want to take Jan 31 2018 and add this date to all previous dates to when someone was first hired, ie.  Jan 31, 2018 - May 13, 1998 and have that count as the number of actives for Jan 2018.  Feb 2018 would be Feb 28, 2018 inclusive to May 13, 1998.   Jan 30, 2017 would include Jan 30, 2017 back to May 13, 1998.   Does that help figure out what I am doing?

 

thanks for your help,

Sue

suegee
Regular Visitor

Re: Employee Count based on Hire/Rehire date

Thanks Cherie,

 

The only way I could get this to work is if I added second date calendar that had just 2017 dates in it, so that it could be the max and then it would work.   Not sure if that's the right way to do it.

Super User IV
Super User IV

Re: Employee Count based on Hire/Rehire date

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors