Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello! I've been tasked with creating a report showing net change in our workforce, by week. Net change = (external hires + contract "starts") - (employee terminations + contract "ends"). I've never really worked with HR data before and I'm stumped on what I think should be straightforward measure. My external hires and terminations measures seem to work fine but my measures of contract "starts" and "ends" return identical results. I understand why but I don't know how to write the correct measure.
The data model. I have a DateDim table and two fact tables EmployeeAssignment and Traveler. DateDim has a DateKey field. There are inactive relationships as follows DateDim[DateKey] to EmployeeAssignmentFact[EnterpriseHireDate] and DateDim[DateKey] to TravelerFact[Start Date].
I created the following measures for new hires and terminations
Count of Hire Date =
CALCULATE (
COUNT ( EmployeeAssignmentFact[EnterpriseHireDT] ),
EmployeeAssignmentFact[HireAndTermSame] = "F",
USERELATIONSHIP ( EmployeeAssignmentFact[AssignmentStartDateKey], DateDim[DateKey] )
)
Count of Term Date =
CALCULATE (
COUNT ( EmployeeAssignmentFact[TermDT] ),
USERELATIONSHIP ( EmployeeAssignmentFact[AssignmentStartDateKey], DateDim[DateKey] )
)
And the following measures for contract starts and ends:
Count of Contract Starts =
CALCULATE (
COUNT ( TravelerFact[Start Date] ),
USERELATIONSHIP ( TravelerFact[Start Date], DateDim[DateKey] )
)
Count of Contract Ends =
CALCULATE (
COUNTA ( TravelerFact[End Date] ),
USERELATIONSHIP ( TravelerFact[Start Date], DateDim[DateValue] )
)
I want to see the number of new hires and contract starts by week. This measure works well for the new hires and terms but it does not work for the traveler contracts. I end up with the same values for both starts and ends.
I understand that by counting start and end dates I'm essentially counting the same thing as each row/contract has both a start and end date. Can someone help me with how to count them? As I said, I'm new to this type of data and fairly new to time intelligence measures. I could really use help differentiating the two!
Sample data for TravelerFact
Index | clinician_id | Start Date | End Date | Contracted Weeks | Weekly Hour | Total Hours | Start Year-Week |
1077 | 34 | 8/2/2022 | 10/29/2022 | 0 | 32 | 416 | 2022-32 |
1080 | 35 | 6/13/2022 | 9/3/2022 | 12 | 40 | 480 | 2022-25 |
1082 | 36 | 6/6/2022 | 9/3/2022 | 12 | 36 | 432 | 2022-24 |
1085 | 38 | 8/15/2022 | 11/12/2022 | 0 | 40 | 520 | 2022-34 |
1088 | 39 | 5/23/2022 | 8/20/2022 | 13 | 40 | 520 | 2022-22 |
1089 | 40 | 8/15/2022 | 11/19/2022 | 14 | 36 | 504 | 2022-34 |
1094 | 43 | 5/29/2022 | 8/27/2022 | 13 | 36 | 468 | 2022-23 |
1096 | 44 | 7/11/2022 | 10/8/2022 | 13 | 36 | 468 | 2022-29 |
1098 | 45 | 4/25/2022 | 7/23/2022 | 13 | 40 | 520 | 2022-18 |
1099 | 45 | 8/8/2022 | 11/5/2022 | 13 | 40 | 520 | 2022-33 |
1078 | 53 | 4/19/2022 | 7/16/2022 | 13 | 36 | 468 | 2022-17 |
1079 | 53 | 7/25/2022 | 10/15/2022 | 12 | 36 | 432 | 2022-31 |
1091 | 65 | 4/25/2022 | 7/23/2022 | 12 | 36 | 432 | 2022-18 |
1092 | 65 | 8/14/2022 | 11/12/2022 | 12 | 36 | 432 | 2022-34 |
1083 | 84 | 4/24/2022 | 7/23/2022 | 13 | 36 | 468 | 2022-18 |
1084 | 84 | 7/24/2022 | 10/1/2022 | 10 | 36 | 360 | 2022-31 |
1075 | 112 | 4/18/2022 | 7/16/2022 | 13 | 40 | 520 | 2022-17 |
1097 | 155 | 4/25/2022 | 7/23/2022 | 13 | 40 | 520 | 2022-18 |
1100 | 201 | 4/25/2022 | 7/30/2022 | 13 | 40 | 520 | 2022-18 |
1081 | 230 | 4/20/2022 | 10/15/2022 | 26 | 48 | 1248 | 2022-17 |
1086 | 238 | 4/24/2022 | 7/23/2022 | 13 | 36 | 468 | 2022-18 |
1087 | 238 | 7/24/2022 | 10/22/2022 | 13 | 36 | 468 | 2022-31 |
1093 | 245 | 4/25/2022 | 7/23/2022 | 13 | 40 | 520 | 2022-18 |
1076 | 250 | 4/18/2022 | 7/16/2022 | 13 | 40 | 520 | 2022-17 |
1095 | 278 | 4/25/2022 | 7/30/2022 | 13 | 40 | 520 | 2022-18 |
1090 | 279 | 4/24/2022 | 8/24/2022 | 18 | 40 | 720 | 2022-18 |
Solved! Go to Solution.
If you can add an end date year-week column to the table then you could use
Groovy! I'll need to validate some more but a quick check and it looks like that appears to do the trick.
Thanks so much!
If you can add an end date year-week column to the table then you could use
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |