Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cathoms
Helper V
Helper V

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

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].

cathoms_5-1661884916282.png

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.

cathoms_6-1661885329163.png

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

Indexclinician_idStart DateEnd DateContracted WeeksWeekly HourTotal HoursStart Year-Week
1077348/2/202210/29/20220324162022-32
1080356/13/20229/3/202212404802022-25
1082366/6/20229/3/202212364322022-24
1085388/15/202211/12/20220405202022-34
1088395/23/20228/20/202213405202022-22
1089408/15/202211/19/202214365042022-34
1094435/29/20228/27/202213364682022-23
1096447/11/202210/8/202213364682022-29
1098454/25/20227/23/202213405202022-18
1099458/8/202211/5/202213405202022-33
1078534/19/20227/16/202213364682022-17
1079537/25/202210/15/202212364322022-31
1091654/25/20227/23/202212364322022-18
1092658/14/202211/12/202212364322022-34
1083844/24/20227/23/202213364682022-18
1084847/24/202210/1/202210363602022-31
10751124/18/20227/16/202213405202022-17
10971554/25/20227/23/202213405202022-18
11002014/25/20227/30/202213405202022-18
10812304/20/202210/15/2022264812482022-17
10862384/24/20227/23/202213364682022-18
10872387/24/202210/22/202213364682022-31
10932454/25/20227/23/202213405202022-18
10762504/18/20227/16/202213405202022-17
10952784/25/20227/30/202213405202022-18
10902794/24/20228/24/202218407202022-18
1 ACCEPTED SOLUTION
jgeddes
Super User
Super User

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 
jgeddes_0-1661890785116.png

 

View solution in original post

2 REPLIES 2
cathoms
Helper V
Helper V

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
Super User
Super User

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 
jgeddes_0-1661890785116.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.