Forum Discussion
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.
| ID | hire/rehire |
| 138 | 3/26/2018 |
| 210 | 3/7/2018 |
| 090 | 1/15/2018 |
| 091 | 1/15/2018 |
| 108 | 1/15/2018 |
| 137 | 1/15/2018 |
| 263 | 1/8/2018 |
| 136 | 1/3/2018 |
| 209 | 12/27/2017 |
| 107 | 12/18/2017 |
| 100 | 12/11/2017 |
| 135 | 12/4/2017 |
| 157 | 12/4/2017 |
| 208 | 10/16/2017 |
| 089 | 10/2/2017 |
| 076 | 9/25/2017 |
| 173 | 9/25/2017 |
| 038 | 9/18/2017 |
| 207 | 8/21/2017 |
| 252 | 8/14/2017 |
| 200 | 8/3/2017 |
| 206 | 7/31/2017 |
| 088 | 7/10/2017 |
| 172 | 7/10/2017 |
| 151 | 5/15/2017 |
| 021 | 5/1/2017 |
| 156 | 5/1/2017 |
| 205 | 3/27/2017 |
| 203 | 3/20/2017 |
| 204 | 3/20/2017 |
| 147 | 2/1/2017 |
| 099 | 1/18/2017 |
| 150 | 1/9/2017 |
| 244 | 12/5/2016 |
| 262 | 11/28/2016 |
| 087 | 10/31/2016 |
| 249 | 10/11/2016 |
| 086 | 10/3/2016 |
| 143 | 10/3/2016 |
| 202 | 10/3/2016 |
| 085 | 9/12/2016 |
| 233 | 9/12/2016 |
| 020 | 8/22/2016 |
| 084 | 8/15/2016 |
| 261 | 8/15/2016 |
| 134 | 7/5/2016 |
| 098 | 5/30/2016 |
| 248 | 5/16/2016 |
| 146 | 5/2/2016 |
| 247 | 4/25/2016 |
| 083 | 3/29/2016 |
| 201 | 3/28/2016 |
| 019 | 2/29/2016 |
| 082 | 2/29/2016 |
| 081 | 2/16/2016 |
| 037 | 2/8/2016 |
| 145 | 1/20/2016 |
| 149 | 1/20/2016 |
| 133 | 11/23/2015 |
| 300 | 11/16/2015 |
| 080 | 9/21/2015 |
| 018 | 9/1/2015 |
| 154 | 8/24/2015 |
| 199 | 8/18/2015 |
| 292 | 8/17/2015 |
| 148 | 8/10/2015 |
| 078 | 6/29/2015 |
| 079 | 6/29/2015 |
| 246 | 6/8/2015 |
| 132 | 6/3/2015 |
| 077 | 5/25/2015 |
| 171 | 5/4/2015 |
| 097 | 4/13/2015 |
| 299 | 4/13/2015 |
| 017 | 3/30/2015 |
| 131 | 3/30/2015 |
| 106 | 3/23/2015 |
| 297 | 2/17/2015 |
| 053 | 1/12/2015 |
| 130 | 1/6/2015 |
| 016 | 1/5/2015 |
| 075 | 1/5/2015 |
| 153 | 12/15/2014 |
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
8 Replies
Hi - Can you check this PBIX file is of any help:
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.
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.
v-cherch-msftMicrosoft Employee
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
- Ashish_Mathur
Super User
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?
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
- Ashish_Mathur
