Forum Discussion
Human Resources Sample File - Employee Table
Hi Everyone
I've been looking at the Human Resources Sample File today in PowerBi Desktop and I think I understand how eveything works and how I could build something similar using my own organsiations data.
One thing I'm struggling why we need the date field as the fist field in the employee table, if we didnt have that there and it was just the employee ID what would happen?
Anonymous,
You may just disable the relationship to see the difference.
Now Understood: The Date column in the Employee Table is used as follows:
In this dataset, once a month, an extract from the source system is produced. For each month that an employee is active they have a line of data on the first of the month as an employee “snapshot” fact table.
I read the "Data Warehouse Toolkit by Ralph Kimball and Margy Ross which has a small but useful section on thinking about models for HR and how to strucutre them.
Hi Anonymous
I cant find the original post I personally found a soultion for, but this is what you can do, create these 4 messurements,
New Hires = COUNTA ("employment hire date")Terminated = CALCULATE(COUNTA(("your employments hire date"), USERELATIONSHIP('Calendar'[Date], ("employment termination date"))Balance = [New Hires] - [Terminated]Overall Current Employees = CALCULATE([Balance], FILTER (ALL('Calendar'[Date]), 'Calendar'[Date] <= MAX ('Calendar'[Date])))P.S Use a generated calander date table to make a relansionship between the hire date and termination date, and you will be good to go :)
6 Replies
Anonymous,
You may just disable the relationship to see the difference.
Hi Anonymous
I cant find the original post I personally found a soultion for, but this is what you can do, create these 4 messurements,
New Hires = COUNTA ("employment hire date")Terminated = CALCULATE(COUNTA(("your employments hire date"), USERELATIONSHIP('Calendar'[Date], ("employment termination date"))Balance = [New Hires] - [Terminated]Overall Current Employees = CALCULATE([Balance], FILTER (ALL('Calendar'[Date]), 'Calendar'[Date] <= MAX ('Calendar'[Date])))P.S Use a generated calander date table to make a relansionship between the hire date and termination date, and you will be good to go :)
Now Understood: The Date column in the Employee Table is used as follows:
In this dataset, once a month, an extract from the source system is produced. For each month that an employee is active they have a line of data on the first of the month as an employee “snapshot” fact table.
I read the "Data Warehouse Toolkit by Ralph Kimball and Margy Ross which has a small but useful section on thinking about models for HR and how to strucutre them.