Forum Discussion
12-Month Rolling Turnover Not Working
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
Super UserHi 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-3d98a5b0e07fI figured out the termination type as well.I just needed to add a line in my Average Headcount calculation so it would pick all the full number when looking at termination type ALL('Employee'[Termination Type]).