[Seek Help] Use Date Table to filter 2 dates in one table

[TomLU123]

Dear Expert, 

I have a table like below which lists down all the employees' Hire Date and Termination Date. Active Employee's termination date will be blank. Once they got terminated, the termination date will have value. Below is just an example:

Employee ID	Hire Date	Job	Termination Date
123	5/1/2019	HR
232	5/1/2019	HR
456	9/5/2017	Finance	5/1/2019
534	5/1/2019	Admin
354	1/14/2018	IT	5/1/2019
345	1/1/2019	IT

What I want to measure is ①Number of New Hire ② Number of Terminated Headcount.

I have another table which lists down all the dates of the year:

Date

5/1/2019

6/1/2019

6/2/2019

6/3/2019

I want to use this Date table has the single filter to calculate those 2 measures at the same time so that I can put into single visual. The idea is like below:

New Hire Headcount = If "Hire Date" = "Date" selected, distinctcount of Employee ID. 

Terminated Headcount = If "Termination Date"="Date" slected, distinctcount of Employee ID.

Which means, if the user choose 5/1/2019 in the filter. The measure for new hire will be 3 and terminated headcount will be 2.

Is it possible to achieve that?

Many thanks!

[parry2k · Accepted solution]

@TomLU123 set relationship between hire date and termination date with your calendar table. One of this relationship will be active and other one will be inactive.

In this case I'm assuing Hire Date relationship is Active and Terminate Date relationship is InActive.

Add following measure and you will get the count of hire and terminate head count as per date.

Hire Headcount = 
DISTINCTCOUNT( Table[EmployeeId] )

Terminate Headcount = 
CALCULATE( [Hire Headcount], 
USERELATIONSHIP( Table[Termination Date], Calendar[Date] )
)

[Cmcmahan]

Sure. Two measures is probably the easiest way to get this info.

TotalHired = CALCULATE( DISTINCTCOUNT(Table1[Employee ID]), Table1[Hire Date] = SELECTEDVALUE(DateTable[Date]) )

TotalTerminated = CALCULATE( DISTINCTCOUNT(Table1[Employee ID]), Table1[Termination Date] = SELECTEDVALUE(DateTable[Date]) )

Stick those into your visual that has dates from your DateTable, and you're good to go