Dax Function - See transfered people

[DrNossberg]

Hello BI community 🙂

I would like to now in Power BI the number of people who changed entreprise

Assigned Number	Legal Start Date	Person Hire Date	Person Termination  Date	Employer	ID	Transfert
E75H3F-2	14/08/2018	12/04/2018		Entreprise A	89456	1
E75H3F	14/08/2018	02/11/2018	10/04/2018	Entreprise B	89456	0
E75H3F	14/08/2018	02/11/2018	10/04/2019	Entreprise B	89456	1
AH1J91	14/08/2018	14/08/2018	30/10/2018	Entreprise A	89456	0
AH1J91	14/08/2018	14/08/2018	30/10/2018	Entreprise A	89456	0

So the transfert column is what I what, following those conditions:

The ID is the same

The Employer is different

The Assigned Number is different too

The Termination date of the previous entry is max 5 days before the hire date

The last is a thought one, and that's why i'm requesting your help.

What I have so far is:

Transfert  = IF(COUNTROWS(CALCULATETABLE('Data';
     FILTER('Data'; 'Data'[ID] = EARLIER('Data'[ID]));
     FILTER('Data'; 'Data'[Employer] <> EARLIER('Data'[Employer]));
     FILTER('Data'; DATEDIFF(EARLIER('Data'[Termination Date]); 'Data'[Hire Date); DAY) <= 5)
     )) > 0 ; 1; 0)

But that's not working, in fact every record concernig a person who has been transfered is set to 1, like this:

Assigned Number	Legal Start Date	Person Hire Date	Person Termination  Date	Employer	ID	Transfert
E75H3F-2	14/08/2018	12/04/2018		Entreprise A	89456	1
E75H3F	14/08/2018	02/11/2018	10/04/2018	Entreprise B	89456	1
E75H3F	14/08/2018	02/11/2018	10/04/2019	Entreprise B	89456	1
AH1J91	14/08/2018	14/08/2018	30/10/2018	Entreprise A	89456	1
AH1J91	14/08/2018	14/08/2018	30/10/2018	Entreprise A	89456	1

Thanks in advance, have a good day ^^

[v-chuncz-msft]

@DrNossberg,

You may check if the following post helps.

https://community.powerbi.com/t5/Desktop/date-difference-from-list-of-related-dates/m-p/569179#M2686...