Employee Count based on Hire/Rehire date

[suegee]

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

[v-cherch-msft · Accepted solution]

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]

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?

[suegee]

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 · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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]

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-msft · Accepted solution]

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

[suegee]

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.

[parry2k]

@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.