Calculate number of contract start and end dates and display by week

[cathoms]

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

[cathoms]

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!

[jgeddes · Accepted solution]

If you can add an end date year-week column to the table then you could use

Contract Starts =

var _singleValue =

CALCULATE(

    COUNT(TravellerFact[clinician_id]),

    TravellerFact[Start Year-Week] = SELECTEDVALUE(DateDim[Year Week])

)

var _totalValue =

CALCULATE(

    COUNT(TravellerFact[clinician_id]),

    TravellerFact[Start Year-Week] >= MIN(DateDim[Year Week]) && TravellerFact[Start Year-Week] <= MAX(DateDim[Year Week])

)

return

IF(

    HASONEVALUE(DateDim[Year Week]),

    _singleValue,

    _totalValue

)

and

Contract Ends =

var _singleValue =

CALCULATE(

    COUNT(TravellerFact[clinician_id]),

    TravellerFact[End Year-Week] = SELECTEDVALUE(DateDim[Year Week])

)

var _totalValue =

CALCULATE(

    COUNT(TravellerFact[clinician_id]),

    TravellerFact[End Year-Week] >= MIN(DateDim[Year Week]) && TravellerFact[End Year-Week] <= MAX(DateDim[Year Week])

)

return

IF(

    HASONEVALUE(DateDim[Year Week]),

    _singleValue,

    _totalValue

)

to get