Forum Discussion
Data rolling measure question
Hi,
I have some Headcount data that includes the position, team, when they started and, if applicable, when they left, together with what positions are established and those who have left. It is formatted like this with example data:
| Position | Department | Start Date | End Date | Current Status |
| Head of Marketing | Marketing | 01/01/2017 | Established | |
| Head of Business | Marketing | 01/06/2017 | 04/05/2019 | Left |
| Head of Technology | Technology | 01/06/2020 | Offer |
I would like to be able to model attrition as a % of current workforce. Specifically I want to be able to model a rolling count of the last 12 months (calculated I'd imagine by doing a rolling 12 month count of the dates in the 'End Date' Column in the past from today back 12M) and taking that as a % of the count of employees marked on the Excel import column (Current Status) as 'Established'.
I am able to do a rolling count of raw numbers but need to have a way of making this dynamic such that it updates with the current date and looks backwards 12M and taking that as a % of the total number of employees marked as established at this current date.
Hope that makes sense as struggling to do so. Any assistance would be greatly appreciated.
2 Replies
Check if this blog on similar topic can help
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on LinkedinThanks for that tip. However I can't see where I can do this rolling attrition from today.
In other words, I want the calculation to work out the number of people who have 'left' in the column 'current status' but to do so over a dynamic time period such that it takes today and minuses 12 months. It would then take that figure and divide it by the total number of employees who have 'established' in their 'current status' column as of the present time.
Could you or anyone else assist specifically with that and do point out of I've missed something but coul;dn't see directly how to calculate that in your post.
