Forum Discussion
12-Month Rolling Turnover Not Working
Need Help with 12 Month Rolling Turnover Calculation
I have attached my PBIX file at the bottom. I am running into issies with my calculation and data relationships.
Objective: Calculate 12-month rolling turnover by using three different tables Master Census Table, Termination Table, and Date Calendar. The turnover should be calculated by considering the total exits and average headcount at the end of each month.
Data Tables
1. Master Census: Includes original hire date, termination date, country, and employee number.
2. Terminations: Includes Employee Number, termination date, and termination reason (for termination type).
3. Date Calendar
Current Approach: Since my table does not have monthly snapshots and is refreshed with a new data set every month I am trying to create my won monthly snapshots with my calculations looking at the Last Day of each month. I have created three seperate calcualtions.
1. Terminations: Count all exits at the last day of each month using termination data.
2. Avg Headcount: Determine the total active employees at the end of each month.
3. 12 Month Rolling Turnover: Terminations/Avg Headcount
Current Challenges:
1. When introducing 'Country' from the Master Census Table, the total seems to aggregate across all countries.
2. Introducing 'Termination Type' from the Termination Table disrupts the calculations.
Possible Issues:
1. Aggregation issues may arise due to incorrect use of relationships between tables or improper data modeling.
2. Termination Type might be affecting the calculations if not handled properly in the data model.
3. Incorrect Dax used for my calculated measures
Link to My Test PBIX File:
https://drive.google.com/file/d/1F8LHfRNHQCz4VW-5WafehLNkdrd_K-Ot/view?usp=sharing
JROCKY1212 , Make sure you have headcount and termination employee
Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Date'[Date]) )
Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Date'[Date]),not(ISBLANK(Employee[End Date])))
Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))
12 Month Avg Current Employees = CALCULATE(AverageX(Values('Date'[MONTH Year]),[Current Employees])
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxAPower BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU
Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f
4 Replies
- gmsamborn
Super User
Hi JROCKY1212
I noticed a couple of things that amy or may not be relevant.
- Relationship between DateCalendar[Date] and ‘Master Census’[Orignal Hire Date] is 1:1 and should be 1:* .
- ‘DateCalendar’[Date] should be Date not Datetime.Also I would merge ‘Master Census’ and ‘Terminations’ keeping [Termination Type] (in Power Query). This will clean up your model a bit.
Hey,
thansk for the reply. I have played around with my relationships and no luck. Also merged the queries in power query to try that out before.
I am thinking it is both my Dax and the relationships I have set up. Any chance you can play around with the file I attached to try and see if you can get it to work? I have tried everything.
- amitchandak
JROCKY1212 , Make sure you have headcount and termination employee
Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Date'[Date]) )
Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Date'[Date]),not(ISBLANK(Employee[End Date])))
Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))
12 Month Avg Current Employees = CALCULATE(AverageX(Values('Date'[MONTH Year]),[Current Employees])
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxAPower BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU
Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f
