Hired and fired on one visual

[Anonymous]

Hi,

i have a table which contains one line per employee and multiple columns included one for the hire date and one for the termination date.

I also have a "date" table.

I want to have in the same visual the number of hired employees and the number of terminated employees per month

it seems very simple but i don't find the solution.

if someone could help me....

Thanks

[Seth_C_Bauer · Accepted solution]

@Anonymous Create a relationship between both dates to your main date table.

In this instance the "Fire Date" is the active relationship so you can use a count of people or create a simple measure

Fire = COUNTA('Table'[Person]) and that will inherit and use the active relationship. For the other relationship we need to specify that you want to use the inactive relationship so your measure would look something like this -> 

Hire = CALCULATE(COUNTA('Table'[Person]), USERELATIONSHIP('Date'[Date],'Table'[Hire Date]))

Then in your visual, you can use the single Date column from the Date dimension and the calculations would use the appropriate date filter.

[Anonymous]

thank you, it works, i juste dicover the USERELARTIONSHIP!

[Nathaniel_C]

Hi @Anonymous ,

If the date table is related to the other table, you should be able to drop it a visual like a table or matrix (assuming you have a monthname in the table, then create a measure COUNT(table[hired date]) and another COUNT(table[fired date]).  The monthname will filter the two measures so that you only get each months count. Of course, use your table and column names in the formula.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel