Need help in calculating leavers and joiners in appended report for a particular cost center

Chitt_ — Wed, 22 Oct 2025 04:21:41 GMT

Hi,
I have a people file which is downloaded daily and saved ina shared path with date.Now we have a requirement to show leavers,joiners,job role changes when 2 snapshot dates are selected and also a cost center is selected.
Below is the sample data.

emp id	name	gender	snapshotdate	costcode
1	chinnari	fe	9/1/2025	123
2	Narayan	M	9/1/2025	143
3	Geethansh	M	9/1/2025	173
4	Thanvika	fe	9/1/2025	153
2	Narayan	M	9/2/2025	133
1	chinnari	fe	9/2/2025	123
5	Jashvin	M	9/2/2025	193
1	Chandana	fe	9/3/2025	123
2	narayan	M	9/3/2025	133
5	Jashvin	M	9/3/2025	203

Leavers=People who left the organisation from the cost center and the people who transferred internally from the selected cost center to another costcenter between the two selected snapshot dates(the person who is in the first snapshot date but not in the secondsnapshot date)
Joiners=People who joined the organisation for that costcenter and people who transferred internally to the selected costcenter from another costcenter between the two selected snapshot dates((the person who is in the second snapshot date but not in the first snapshot date)
Job role changes=People who has changed the jobrole between the two selected snapshot dates for that particular cost center selected.
I have used the below code and it is working fine for sample data,but when it comes to my real data which I cannot post here, I am getting count wrong in few cases not all cases.For example if the person is available in both the dates for that selected costcenter it is also getting calculated.

CurSnapshot = IF(ISFILTERED(people[snapshotdate])=FALSE(),

CALCULATE(MAX(people[snapshotdate]), ALL(people)),

CALCULATE(MAX(people[snapshotdate]),

PrevSnapshot =

VAR FilterCheck = ISFILTERED(people[snapshotdate])

VAR ValMin = CALCULATE(MIN(people[snapshotdate]),

ALLEXCEPT(people,people[snapshotdate]))

VAR ValMax = CALCULATE(MAX(people[snapshotdate]),

ALLEXCEPT(people,people[snapshotdate]))

VAR ValCheck = (ValMin = ValMax)

VAR PriorRank = LOOKUPVALUE(people[snapshot_Rank],

people[snapshotdate], [CurSnapshot])+1

VAR PriorSnapshot = LOOKUPVALUE(people[snapshotdate],

people[Snapshot_Rank], PriorRank)

RETURN IF(OR(FilterCheck=FALSE,ValCheck=TRUE()), PriorSnapshot , ValMin)

ALLEXCEPT(people,people[snapshotdate])))

Table 1 =

VAR PREV=[PrevSnapshot]

vAR CUR=[CurSnapshot]

VAR CC=SELECTEDVALUE(people[costcode])

vAR RESULT=

CALCULATETABLE(ADDCOLUMNS(people,"MISSING",

ISBLANK(LOOKUPVALUE(people[emp id],people[emp id],people[emp id],

people[snapshotdate],CUR,people[costcode],people[costcode]))))

RETURN RESULT
Result

COUNT = SUMX('Table 1',IF('Table 1'[MISSING]=TRUE(),1,0))
Result:

You can see the above result is correct but when it comes to my real data when I take the sanpshot dates from people table count is not working as it is from calculated table,and when I am taking every slicer from calculated table for few of the cases it is giving true for a person who is available in both the snapshotdates.

please help me in achieving this as I am struggling from many days.
Thanks in advance.

Re: Need help in calculating leavers and joiners in appended report for a particular cost center

lbendlin — Thu, 23 Oct 2025 00:33:09 GMT

The easiest approach to comparing snapshots is to use EXCEPT and INTERSECT.

EXCEPT(old,new) = leavers

EXCEPT(new, old) = joiners

INTERSECT(old,new) = remainers

Re: Need help in calculating leavers and joiners in appended report for a particular cost center

v-hjannapu — Fri, 24 Oct 2025 06:56:02 GMT

Hi @Chitt_ ,

I would also take a moment to thank @lbendlin , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

Re: Need help in calculating leavers and joiners in appended report for a particular cost center

v-hjannapu — Mon, 27 Oct 2025 11:43:48 GMT

Hi @Chitt_,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.

Regards,
Community Support team.

topic Re: Need help in calculating leavers and joiners in appended report for a particular cost center in Developer

Need help in calculating leavers and joiners in appended report for a particular cost center

Re: Need help in calculating leavers and joiners in appended report for a particular cost center

Re: Need help in calculating leavers and joiners in appended report for a particular cost center

Re: Need help in calculating leavers and joiners in appended report for a particular cost center