Count rehired employees

[EllePi]

Hi there,

i'm strugling with this problem:

i have a table with "CF employees", "hiring date", "termination date"

CF employees	hiring date	termination date
rrrrr	01/01/2019	31/12/2020
wwww	01/01/2020
ttttt	01/02/2022	31/03/2022
ttttt	01/03/2023
zzzzz	01/11/2023

i've created wih DAX some new measures:

"Hired Employees" = CALCULATE(COUNT(TURNOVER[CF]),USERELATIONSHIP(TURNOVER[HIRINGdata],'Calendar'[Date]) )

"TerminetedEmployees"  =  CALCULATE(COUNT(TURNOVER[CF]),USERELATIONSHIP(TURNOVER[TERMdate],'Calendar'[Date]),not(ISBLANK(TURNOVER[TERMdate])))

both measure counts in the right way all hired and terminated employees

the i have a grafic with the result of the 2 measures, and i'm able to filter the graphic by YEAR of hiring and termination havig the right number for the filtered year.

Now i need a new measure: i need to count how many employees has been REHIRED, that new measure has to be sensible to the hiring YEAR filter of the graphic; 

i'm only able to count all employees rehierd but that number is the total count and not sensible to the YEAR filter of the graphic.

"Rehired" = CALCULATE(COUNTROWS(TURNOVER), ALLEXCEPT(TURNOVER, TURNOVER[CF]), FILTER(VALUES(TURNOVER[CF]),

        CALCULATE(COUNTROWS(TURNOVER), ALLEXCEPT(TURNOVER, TURNOVER[CF]),TURNOVER[CF] = EARLIER(TURNOVER[CF]))>1))

the result of that one is the total count of all rehired employees although I've filtered the hiring year in the graphic;

if i filter by year in the graphic:
- i have the correct count of Hired for that year (correct)
- i have the correct count of Terminated for thar year (correct)
- i have the the all count of the Rehired of all the years (uncorrect)

Thanks for any tip to find a simple solution, i know it's simple but i'm not so skilles in dax!

[speedramps · Accepted solution]

Try this ...

Click here to download PBIX from OneDrive 

How it works ...

Create a calanader table with Year column and data relationship to your fact table

Create measure to detemine is row is a rehire

Rehire = 
var mydate = SELECTEDVALUE(yourdata[hiring date])
var myemployee= SELECTEDVALUE(yourdata[CF employees])
RETURN
CALCULATE(
    INT(NOT(ISEMPTY(yourdata))),
    ALL(),
    yourdata[CF employees] = myemployee,
    yourdata[hiring date] < mydate
)

The wrap the measure in a SUMX so you can get the rehired total for a year

Rehired = 
SUMX(VALUES(yourdata[CF employees]),[Rehire])

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

[amustafa]

Based on your sample dataset, do you consider new hire if the hiring date is greater than termination date? what is the rule to identify new hire?

[EllePi]

Thanks amustafa,

my data set it really simple:

HIRED ->  i count every hired employees, i filter the year in the graphic and the result is the number of hired in that year considering the hiring date; for this measure it's not important if the employess has got a termination date

TERMINATED -> i count every terminated employees, i filter the year in the graphic and the result is the number of terminated in that year considering if term date in not blank

now i'm trying to have as result the employees "duplicated": they've 2 o more hiring date and 2 or more termination date; i'd to filter the graphic by date and have in return the number of rehired employees in that year.

Hope i've been able to explain in the right way.
Thanks