Forum Discussion
Human Resources Sample File - Employee Table
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 :)
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.
Makes sense, cant belive I dident figure it out, its so obvious now, especially when looking at single emp ID's :)
Thanks Anonymous