Help with Dax formula - Department Transfers

[AkameNoGamma]

I am looking for a formula that can calculate department transfers based on the effective date of an employee's move from one department to another. My goal is to compute the opening and closing headcount for each department every month. While I have the formula for new hires and separations, I need to include the count for department transfers to complete the data. I have searched for guides on headcount and attrition computations, but none of them take department transfers into account.

I have attached a table that I currently have on my dashboard, which is missing the columns for "Move In" and "Move Out".

File Link: https://docs.google.com/spreadsheets/d/17r-CCpVN0IEgnZlg9ShjycDVKhhvGDhJ/edit?usp=drive_link&ouid=11...

Expected Result

as of April 2023
Department	Opening Headcount	New Hires	Separation	Move in	Move Out	Closing Headcount
Air Business Subscriptions	151		5		1	145
Amex US	30	15	2		1	42
Audit &Certification	2		1	1		2
Beach	25	20		1		46
Bose	30				2	28
CBE	22	23		1		46
Gurus	19		2	1		18
McDonalds	15		2	2	2	13
New Wonder	100	10		2		112
Quest	16	5		2	3	20
Training	21		1	1		21
Vizio	30	3	3		2	28

Sample Data (which is also transcribe in the table above)

Local ID	Effective Start Date	New Department	Previous Department
M150539	4/3/2023	Beach	Air Business Subscriptions
M124452	4/1/2023	CBE	Amex US
M97563	4/17/2023	Audit &Certification	Bose
M95745	4/25/2023	Training	Bose
M137793	4/24/2023	Quest	McDonalds
M148965	4/25/2023	Quest	McDonalds
M140355	4/17/2023	Gurus	Quest
M137183	4/24/2023	New Wonder	Quest
M139200	4/24/2023	New Wonder	Quest
M149475	4/10/2023	McDonalds	Vizio
M149476	4/10/2023	McDonalds	Vizio

[v-jialongy-msft · Accepted solution]

Thank you for your prompt reply! @lbendlin 

Hi @AkameNoGamma 

Please try the following Dax:

Move in:

Move in = 
VAR department =SELECTEDVALUE('Table'[Department])
RETURN CALCULATE(COUNTROWS('Table (2)'),FILTER('Table (2)','Table (2)'[New Department] = department))

move out:

Move out = 
VAR department =SELECTEDVALUE('Table'[Department])
RETURN CALCULATE(COUNTROWS('Table (2)'),FILTER('Table (2)','Table (2)'[Previous Department] = department))

Result:

Result = SELECTEDVALUE('Table'[Opening Headcount]) + SELECTEDVALUE('Table'[New Hires]) + [Move in] - [Move out] - SELECTEDVALUE('Table'[Separation])

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AkameNoGamma]

Thank you so much! This absolutely solved my problem, but now i'm in a rabbit hole because of a new problem. i'll post it on a different thread. thanks!!!

[lbendlin]

The google link asks for access. Please check.

[AkameNoGamma]

weird, i thought i already updated the share link. check now if it works.

[lbendlin]

Your sample data is not matching the expected outcome. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[AkameNoGamma]

Thank you for looking this out. I believe i was able to provide the sample data and it is just a simple table with the basic formula to get the closing headcount which involves the following parameters:

Closing Headcount = (Opening Headcount + New Hires + Move In) - (Separations + Move Out).

In my table i only provided the list of move in and out since i already have the dax for the rest of the parameters above.

can you point out what is the issue with my "expected result"?

[lbendlin]

I cannot assist you if you are unable to provide meaningful sample data. I hope someone else can help you further.