Multiple date columns - Employee Turnover Calculation - pbix attached

Question

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 · Answer

You say you have two inactive relationships. That means you have to specify the USERELATIONSHIP() modifier all the time, in all your measures.
&nbsp;
Usually the more dominant relationship is kept active, which saves some typing.
&nbsp;
How are your users specifying the date intervals?&nbsp; Or are these immutable?

jeags · Answer

Does that mean I need to also modify the above Headcount measure?&nbsp;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.&nbsp;Hope that helps clarify!&nbsp;Headcount =CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;COUNTROWS&nbsp;(&nbsp;'KAT'&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;VALUES&nbsp;(&nbsp;'KAT'[Hire Date]&nbsp;),&nbsp;'KAT'[Hire Date]&nbsp;&lt;=&nbsp;MAX&nbsp;(&nbsp;'Date'[Date]&nbsp;)&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(&nbsp;'KAT'[Termination Date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OR&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'KAT'[Termination Date]&nbsp;&gt;=&nbsp;MIN&nbsp;(&nbsp;'Date'[Date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISBLANK&nbsp;(&nbsp;'KAT'[Termination Date]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))

lbendlin · Answer

yes, you need to modify all measures. How else will they know which relationship to use?
&nbsp;
Your last code example can be simplified quite a bit
&nbsp;
Headcount =CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;COUNTROWS&nbsp;(&nbsp;'KAT'&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;'KAT'[Hire Date]&nbsp;&lt;=&nbsp;MAX&nbsp;(&nbsp;'Date'[Date]&nbsp;)&nbsp;,&nbsp; &nbsp; COALESCE(&nbsp;'KAT'[Termination Date],&nbsp;MIN&nbsp;(&nbsp;'Date'[Date]&nbsp;))&nbsp;&gt;=&nbsp;MIN&nbsp;(&nbsp;'Date'[Date]&nbsp;),)
&nbsp;
but it would still be wrong as your MAX and MIN calculations are impacted by the CALCULATE context change. Use variables.

jeags · Answer

Thank you, can you share what the calculation would look like with variables?&nbsp;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

Forum Discussion

Multiple date columns - Employee Turnover Calculation - pbix attached

Related content

Calculate Other Trained Employees

Counting Active employees per month

Missing employee

CALCULATE with multiple filters

Employee FTE calculation