Measure to show headcount by date. Have employee roster with start and end date .pbix attached

[Anonymous]

I have a simple table that is an employee roster that lists headcount (FTEs or Full Time equivalent) with start date and end date for all employees. I have a double connection in my data model to a date table on Hire Date and Last Day Worked. I've attached an example of the .pbix file here: https://1drv.ms/u/s!AvCirXUmRp-JhcBoiI3j8_UBmo0Bww?e=lJc2xo

I'm trying to show how total headcount changes over time. I believe I need to create a measure that uses crossfilter but it's not working for me.

Below is an example of the data and the visual I'm trying to create

For example, total headcount on 6/16/19 is 2.5 because we have two employees hired on 4/1/17 plus a 0.5 FTE employee hired on 2/15/19. The employee hired on 3/1/18 is not included in headcount on 6/16/19 because this employee last day worked is 6/15/19 so it is excluded. 

I think this can be done with a measure but I'm stuck. 

[jdbuchanan71 · Accepted solution]

@Anonymous 

Can you tell me the other filters you had to get to the 2.5 number (Personnel Area / Subarea etc)?  You should be able to disconnect the date table then use a measure like this to get to your number.

Measure = 
VAR _FirstDate = MIN ( 'Date Table'[Date] )
VAR _LastDate = MAX ( 'Date Table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Tennant EE Headcount'[FTE] ),
        FILTER (
            ALL (
                'Tennant EE Headcount'[Employment Details Hire Date],
                'Tennant EE Headcount'[Employment Details Last Date Worked]
            ),
            'Tennant EE Headcount'[Employment Details Hire Date] <= _LastDate
                && OR (
                    'Tennant EE Headcount'[Employment Details Last Date Worked] >= _FirstDate,
                    ISBLANK ( 'Tennant EE Headcount'[Employment Details Last Date Worked] )
                )
        )
    )

[jdbuchanan71 · Accepted solution]

@Anonymous 

Can you tell me the other filters you had to get to the 2.5 number (Personnel Area / Subarea etc)?  You should be able to disconnect the date table then use a measure like this to get to your number.

Measure = 
VAR _FirstDate = MIN ( 'Date Table'[Date] )
VAR _LastDate = MAX ( 'Date Table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Tennant EE Headcount'[FTE] ),
        FILTER (
            ALL (
                'Tennant EE Headcount'[Employment Details Hire Date],
                'Tennant EE Headcount'[Employment Details Last Date Worked]
            ),
            'Tennant EE Headcount'[Employment Details Hire Date] <= _LastDate
                && OR (
                    'Tennant EE Headcount'[Employment Details Last Date Worked] >= _FirstDate,
                    ISBLANK ( 'Tennant EE Headcount'[Employment Details Last Date Worked] )
                )
        )
    )

[Anonymous]

I had the wrong example file, try this one it will make more sense: https://1drv.ms/u/s!AvCirXUmRp-JhcBoiI3j8_UBmo0Bww?e=lJc2xo

[jdbuchanan71 · Accepted solution]

It would be the same measure.  Disconnect the date table then this one will work.

Measure = 
VAR _FirstDate = MIN ( 'Date Table'[Date] )
VAR _LastDate = MAX ( 'Date Table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Employee Table'[FTE] ),
        FILTER (
            ALL (
                'Employee Table'[Hire Date],
                'Employee Table'[Last Day Worked]
            ),
            'Employee Table'[Hire Date] <= _LastDate
                && OR (
                    'Employee Table'[Last Day Worked] >= _FirstDate,
                    ISBLANK ( 'Employee Table'[Last Day Worked] )
                )
        )
    )