Counting Joiners & Leavers in Snapshot-Appended Report Based On Report Date

[Twizxup]

Hi All,

I receive a weekly report that I append to the previously received reports each week. This report includes the Report Date, Employee ID, Name, and Division. For this example, pretend I am in the HR division. I need to use this report to display the number of:

New Hires (i.e. if they weren't on last week's report but are on this week's report)

Transfer-Ins (i.e. if they were in a different division last week but are in HR this week)

Transfer-Outs (i.e. if they were in HR last week but are in a different division this week)

Terminations (i.e. if they were on last week's report but are not on this week's report)

Sample data:

Report Date	Employee ID	Name	Division
7/31/2024	1	John	Sales
7/31/2024	2	Jeremy	Sales
7/31/2024	3	Jacob	HR
8/7/2024	1	John	Sales
8/7/2024	2	Jeremy	Sales
8/7/2024	3	Jacob	HR
8/7/2024	4	Jessica	Finance
8/14/2024	1	John	Sales
8/14/2024	2	Jeremy	HR
8/14/2024	3	Jacob	HR
8/14/2024	4	Jessica	Finance
8/21/2024	2	Jeremy	HR
8/21/2024	3	Jacob	HR
8/21/2024	4	Jessica	Finance
8/21/2024	5	Julian	HR
8/21/2024	6	Jamie	HR
8/28/2024	3	Jacob	HR
8/28/2024	4	Jessica	Finance
8/28/2024	5	Julian	HR
8/28/2024	6	Jamie	Finance

Explanations:

Report Date	Movement Type	Count	Explanation
8/7/2024	New Hire	1	Jessica joined the company
	Transfer-In	0
	Transfer-Out	0
	Termination	0
8/14/2024	New Hire	0
	Transfer-In	1	Jeremy transferred from Sales to HR (our department)
	Transfer-Out	0
	Termination	0
8/21/2024	New Hire	2	Julian and Jamie both joined the company
	Transfer-In	0
	Transfer-Out	0
	Termination	1	John left the company
8/28/2024	New Hire	0
	Transfer-In	0
	Transfer-Out	1	Jamie transferred from HR (our department) to Finance
	Termination	1	Jeremy left the company

Movement summary:

Report Date	Movement Type	Count
8/7/2024	New Hire	1
8/14/2024	Transfer-In	1
8/21/2024	New Hire	2
8/21/2024	Termination	1
8/28/2024	Transfer-Out	1
8/28/2024	Termination	1

I'm having trouble uploading images but ultimately, I'll group New Hires & Transfer-Ins as "Joiners", and Terminations & Transfer-Outs as "Leavers". These counts will be displayed in two separate stacked bar charts (one for Joiners & one for Leavers) where the x-axis is Report Date, y-axis is count, and legend is Movement Type.

Is anyone able to help me with how I should go about doing this? Thank you in advance for your help!

[lbendlin]

New and returning customers – DAX Patterns

[Twizxup]

Thanks for the referral!

This has helped with New Hires & Terminations as the logic follows the same as the article i.e. were they in the previous report (at all) and are they in this report (at all)?

However, I'm struggling with the Transfer-Ins & Transfer-Outs which don't care about you not being in the report, but rather care about the value in a specific field (i.e. Division) and whether or not that value changes week over week.

I suspect this logic should be very similar (and likely easier) but I'm struggling for whatever reason.

Thanks