Hello,
I need help creating 12-month rolling turnover calculation. I also need to show all terminations and averageheadcount. I want to create a trend chart that shows 12 month rolling turnover over 12 the past 12 months. I also want to show some KPIs for terminations, average headcount and 12 monthrolling turnover for the past 12 months.
In my data set I am being provided they dont break it out bu monthly snapshots. They just provide a new file I plan on loading in every month so I cant use the monthly snapshot logic I have found. I am thinking if I take a count of all the terminations based on month start and end dates, then do the same for all active employees each month to give me my monthly average headcount and divide the 2 togother.
I have added some test data below if someone could help me out. I cant share my current report as it has senstive data. I am hoping someone could use my test data and try to help
I have 3 tables in my data model. 1. Master Census 2. Terminations 3. Data Calendar.
My first problem is I can only have one active relationship between hire date and calendar date. When I try to pull average headcount in to view by country it is just showing a total for each row. I have an inactive relationship between termination date and calendar date.
My second problem is they provide me with a seperate termiation table. I need this table to show involuntary and voluntary. I have set up a relationship between the 2 tables using employee ID but when I try to view any of my calculation for turnover they look whack.
https://docs.google.com/spreadsheets/d/1nCLE_CZYT-TVXIeoaNDlkEoqO9ZjQBD0/edit?usp=sharing&ouid=10765...
