topic Help with Dax formula - Department Transfers in DAX Commands and Tips

Help with Dax formula - Department Transfers

AkameNoGamma — Wed, 08 May 2024 12:28:17 GMT

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=117077783427924685771&rtpof=true&sd=true

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

Re: Help with Dax formula - Department Transfers

lbendlin — Wed, 08 May 2024 23:21:27 GMT

The google link asks for access. Please check.

Re: Help with Dax formula - Department Transfers

AkameNoGamma — Thu, 09 May 2024 07:06:02 GMT

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

Re: Help with Dax formula - Department Transfers

lbendlin — Thu, 09 May 2024 22:30:23 GMT

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.

Re: Help with Dax formula - Department Transfers

AkameNoGamma — Fri, 10 May 2024 05:43:18 GMT

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"?

Re: Help with Dax formula - Department Transfers

lbendlin — Fri, 10 May 2024 11:36:23 GMT

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

Re: Help with Dax formula - Department Transfers

Anonymous — Thu, 16 May 2024 02:42:14 GMT

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.

Re: Help with Dax formula - Department Transfers

AkameNoGamma — Thu, 16 May 2024 13:53:44 GMT

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!!!