Multiple date columns - Employee Turnover Calculation - pbix attached

[jeags]

I am having trouble writing DAX that incorporates two different date columns. My pbix file can be found here. 

I have two tables:

1. KAT: A scrubbed table of employees with Employee ID, Hire Date, Termination Date, and Location.

2. Date table

I have two inactive relationships: 'Date'[Date]/'KAT'[Hire Date] and 'Date'[Date]/'KAT'[Termination Date]

I am trying to calculate:

- Monthly Turnover (e.g. January 2020)

- Monthly Turnover for that month the previous year (e.g. January 2019)

- Year to date turnover (e.g. January to June 2020)

- Year to date turnover the previous year (e.g. January to June 2019)

Turnover should be calculated by: number of terminations/average monthly headcount

Examples:

- Monthly turnover Jan 2020 = number of terminations in January 2020/January 2020 headcount

- Year to date turnover in March 2020 = (# of terms in Jan + # of terms in Feb + # of terms in Mar)/(Jan headcount + Feb headcount+ March headcount)/3

And then I want to be able to filter by Location. 

My ideal outcome visual will be a line graph that shows monthly turnover for this year compared to last year. Axis= 'Date'[Year Month], Values = Voluntary turnover and voluntary turnover for previous year

I have been able to get Headcount, Monthly Term Count, Term Count YTD, and Terms Count YTD SPLY but haven't been able to get a calculation to work for turnover. I am having difficulty acknowledging the termination date and hire date relationship and making sure I am still able to filter. Any help or guidance would be greatly appreciated! 

Headcount =
CALCULATE (
    COUNTROWS ( 'KAT' ),
    FILTER ( VALUES ( 'KAT'[Hire Date] ), 'KAT'[Hire Date] <= MAX ( 'Date'[Date] ) ),
    FILTER (
        VALUES ( 'KAT'[Termination Date] ),
        OR (
            'KAT'[Termination Date] >= MIN ( 'Date'[Date] ),
            ISBLANK ( 'KAT'[Termination Date] )
        )
    )
)

Term Count =
CALCULATE (
    COUNT ( KAT[Employee ID] ),
    FILTER ( KAT, NOT ( ISBLANK ( KAT[Termination Date] ) ) ),
    USERELATIONSHIP ( KAT[Termination Date], 'Date'[Date] )
)

Terms Count YTD =
IF (
    [ShowValueForDates],
    CALCULATE ( [Term Count], DATESYTD ( 'Date'[Date] ) )
)

Terms Count YTD SPLY =
IF (
    [ShowValueForDates],
    CALCULATE (
        [Terms Count YTD],
        CALCULATETABLE (
            DATEADD ( 'Date'[Date], -1, YEAR ),
            'Date'[DateWithData] = TRUE
        )
    )
)

[lbendlin]

yes, you need to modify all measures. How else will they know which relationship to use?

Your last code example can be simplified quite a bit

Headcount =
CALCULATE (
    COUNTROWS ( 'KAT' ),
    'KAT'[Hire Date] <= MAX ( 'Date'[Date] ) ,
    COALESCE( 'KAT'[Termination Date], MIN ( 'Date'[Date] )) >= MIN ( 'Date'[Date] ),
)

but it would still be wrong as your MAX and MIN calculations are impacted by the CALCULATE context change. Use variables.

[jeags]

Thank you, can you share what the calculation would look like with variables?

And do you know how I would do the monthly and year-to-date turnover measures?

For example:

- Monthly turnover Jan 2020 = number of terminations in January 2020/January 2020 headcount

- Year to date turnover in March 2020 = (# of terms in Jan + # of terms in Feb + # of terms in Mar)/(Jan headcount + Feb headcount+ March headcount)/3

[lbendlin]

You say you have two inactive relationships. That means you have to specify the USERELATIONSHIP() modifier all the time, in all your measures.

Usually the more dominant relationship is kept active, which saves some typing.

How are your users specifying the date intervals?  Or are these immutable?

[jeags]

Does that mean I need to also modify the above Headcount measure?

The date intervals are either monthly, year-to-date, or year-to-date of the previous year. The users will want to know the number of hires, number of terminations, and the (average) headcount for each of those intervals.

Hope that helps clarify!

Headcount =
CALCULATE (
    COUNTROWS ( 'KAT' ),
    FILTER ( VALUES ( 'KAT'[Hire Date] ), 'KAT'[Hire Date] <= MAX ( 'Date'[Date] ) ),
    FILTER (
        VALUES ( 'KAT'[Termination Date] ),
        OR (
            'KAT'[Termination Date] >= MIN ( 'Date'[Date] ),
            ISBLANK ( 'KAT'[Termination Date] )
        )
    )
)