Calculating Joiners & Leavers in Cumulative/Appended Report

[Twizxup]

Hi All,

I receive a report every week that consists of our current workforce and, each week, I append it to the previous week's report. My ultimate goal is to allow the user select a specific Report Date and, based on that selection, display how many people have joined and left between the previous week's report and the selected report. What's important to note is that I need to be able to differentiate between people that transferred internally and people that joined/left externally (i.e. appeared or disappeared in the report).

Below is a sample dataset that includes the critical fields I'm working with. Some context on existing fields:

Concat 1 = if Count Resource = 1 and Flag = Y then concatenate Department + Resource ID

Concat 2 = concatenate Resource ID + Resource Name + Count Resource

The calculations I need help with are below:

Leaver = if Concat1 = null then 0 otherwise look up Concat1 in the next report date and if it exists then 0 otherwise 1

Leaver Elimination = if Leaver = 0 then 0 otherwise look up Concat2 in the next report date and if it exists then 0 otherwise 1

Joiner = if Concat1 = null then 0 otherwise look up Concat1 in the previous report date and if it exists then 0 otherwise 1

Joiner Elimination = if Joiner = 0 then 0 otherwise look up Concat2 in the previous report date and if it exists then 0 otherwise 1

Report Date	Report Date Index	Department	Resource Name	Resource ID	Flag	Count Resource	Concat1	Concat2	Leaver	Leaver Elimination	Joiner	Joiner Elimination
1/1/2025	1	Finance	Alex	101	N	1		101Alex1	0	0	0	0
1/1/2025	1	Sales	Jared	102	Y	1	Sales102	102Jared1	0	0	0	0
1/1/2025	1	Sales	Rebecca	103	N	0		103Rebecca0	0	0	0	0
1/1/2025	1	Finance	Stacy	104	Y	1	Finance104	104Stacy1	1	0	0	0
1/1/2025	1	HR	Gerard	105	Y	0		105Gerard0	0	0	0	0
1/1/2025	1	HR	Adam	106	Y	1	HR106	106Adam1	0	0	0	0
1/1/2025	1	Sales	Kristin	107	N	1		107Kristin1	0	0	0	0
1/8/2025	2	Finance	Alex	101	N	1		101Alex1	0	0	0	0
1/8/2025	2	Sales	Jared	102	Y	1	Sales102	102Jared1	1	1	0	0
1/8/2025	2	Sales	Rebecca	103	N	0		103Rebecca0	0	0	0	0
1/8/2025	2	Sales	Stacy	104	Y	1	Sales104	104Stacy1	0	0	1	0
1/8/2025	2	HR	Gerard	105	Y	0		105Gerard0	0	0	0	0
1/8/2025	2	HR	Adam	106	Y	1	HR106	106Adam1	0	0	0	0
1/8/2025	2	Sales	Kristin	107	N	1		107Kristin1	0	0	0	0
1/15/2025	3	Finance	Alex	101	N	1		101Alex1	0	0	0	0
1/15/2025	3	Sales	Rebecca	103	N	0		103Rebecca0	0	0	0	0
1/15/2025	3	Sales	Stacy	104	Y	1	Sales104	104Stacy1	0	0	0	0
1/15/2025	3	HR	Gerard	105	Y	0		105Gerard0	0	0	0	0
1/15/2025	3	HR	Adam	106	Y	1	HR106	106Adam1	0	0	0	0
1/15/2025	3	Sales	Kristin	107	N	1		107Kristin1	0	0	0	0
1/15/2025	3	HR	Madison	108	Y	1	HR108	108Madison1	0	0	1	1

Calculation examples:

Leaver = Stacy transferred from Finance to Sales between 1/1/2025 and 1/8/2025, so she's counted as a Leaver (internal) on 1/1/2025

Leaver and Leaver Elimination = Jared appeared in the 1/8/2025 report but not in the 1/15/2025 report, so he's counted as a Leaver Elimination (terminated) on 1/8/2025

Joiner = Stacy transferred to Sales from Finance between 1/1/2025 and 1/8/2025, so she's counted as a Joiner (internal) on 1/8/2025

Joiner and Joiner Elimination = Madison appeared in the 1/15/2025 report but not in the 1/8/2025 report, so she's counted as a Joiner Elimination (new hire) on 1/15/2025

I suspect once the logic for one of these fields is figured out, the rest can be easily replicated. Any help would be hugely appreciated! Thank you in advance!

[ryan_mayu · Accepted solution]

@Twizxup 

you can try this

Column =

var _next=maxx(FILTER('Table','Table'[Report Date Index]=EARLIER('Table'[Report Date Index])+1&&'Table'[Concat1]=EARLIER('Table'[Concat1])),'Table'[Concat1])

return if('Table'[Concat1]="",0,if('Table'[Report Date Index]=max('Table'[Report Date Index]),0,if(_next<>"",0,1))

)

Column 2 =

var _next=maxx(FILTER('Table','Table'[Report Date Index]=EARLIER('Table'[Report Date Index])+1&&'Table'[Concat2]=EARLIER('Table'[Concat2])),'Table'[Concat2])

return if('Table'[Column]=0,0,if('Table'[Report Date Index]=max('Table'[Report Date Index]),0,if(_next<>"",0,1))

)

Column 3 =

var _last=maxx(FILTER('Table','Table'[Report Date Index]=EARLIER('Table'[Report Date Index])-1&&'Table'[Concat1]=EARLIER('Table'[Concat1])),'Table'[Concat1])

return if('Table'[Concat1]="",0,if('Table'[Report Date Index]=min('Table'[Report Date Index]),0,if(_last<>"",0,1))

)

Column 4 =

var _last=maxx(FILTER('Table','Table'[Report Date Index]=EARLIER('Table'[Report Date Index])-1&&'Table'[Concat2]=EARLIER('Table'[Concat2])),'Table'[Concat2])

return if('Table'[Column 3]=0,0,if('Table'[Report Date Index]=min('Table'[Report Date Index]),0,if(_last<>"",0,1))

)

pls see the attachment below

[Twizxup]

Thank you!! This works exactly as expected, I'll mark it as solved. Thanks for the quick response, huge help!

[ryan_mayu]

you are welcome

[Twizxup]

This is working exactly as I had hoped except for one thing that I'm hoping you can help with..

Instead of having the user select the Report Date, I have them select the Index (which I display as "Week #"). When the user selects an Index, I actually want to give the Leaver and Leaver Elimination values for the selected Index minus 1. So, for example: if the user selects Index 2, I want to return the Joiner and Joiner Elimination values for 1/8/2025 (Index 2), and the Leaver and Leaver Elimination values for 1/1/2025 (Index 1).

I assume this can be done with a slight adjustment to the formula, however I'm having trouble. Any help would be awesome!

Thanks again!

[ryan_mayu]

@Twizxup 

pls see if this is what you want

[Twizxup]

Hi Ryan,

Thanks for the reply. Unless I'm missing something, I don't see a difference in the dashboard you attached. Using your file, I put together a matrix (see below, I'm not able to attach a screenshot for some reason).

Slicer filter: Index = 2

Resource Name	Column 3 (Joiner)	Column (Leaver)
Adam	0	0
Alex	0	0
Gerard	0	0
Jared	0	1
Kristin	0	0
Rebecca	0	0
Stacy	1	0
Total	1	1

This is still showing Jared as a leaver in period 2 (which aligns with the table I provided in the original post), however I now want all of the leavers for the selected Index minus 1 (so in the case of Index 2 being selected, I want the leavers for Index 1 which would be Stacy).

I hope this makes sense.. Please let me know if there's anything I can clarify, and thank you again for your help on this!

[ryan_mayu]

@Twizxup 

pls see if this is what you want