Forum Discussion
Helper IIIAverage Active EEs for Date Range
I created the measure below and it works for now! Thanks so much for all your help!!
Helper IIIGreg_Deckler This seems to work though it doesnt include Rehires. Employee should also be counted as an active employee if has a rehire date before the selected date and status is Active or if terminated the termination date is after date selected
Thanks for your time!
Community ChampionMStark Yeah, I considered rehires but I wasn't sure how that worked in your system. Does the employee get an additional row? Does the employee ID stay the same? If both of those are "yes" then you could potentially solve it using a SUMMARIZE and make sure you grab the MAX start date and then essentially lookup the corresponding termination date for each row. Messy.
- MStark
Greg_Deckler Employee # stays the same but an additional row is not added. Theres a separate column named Rehire Date (see sample data on original post)
I was thinking of adding 2 more Var for Rehires on first day selected and rehires on last day selected. Then to add it to the average. Did something like this but its not working. Think maybe the issue is that its counting employees who dont have a rehire date instead of only rehires <= Max or Min dates
Any advise on what Im doing wrong or a different way to get this done?
Thanks for taking the time to look into this!
- Greg_Deckler
MStark Maybe tank your __RF and __RL variables and in your __F and __L variables where you refer to [Hire Date] replace that column reference with MAX([Hire Date, [Rehire Date])
- MStark
I created the measure below and it works for now! Thanks so much for all your help!!