Data rolling measure question

[tobiasmcbride]

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.

[amitchandak]

Check if this blog on similar topic can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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 Linkedin

[tobiasmcbride]

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