Pivot with slicers - Employee Data, Term data

[zeeworld]

Good day,

I want to start by saying how grateful I am for this community. This forum has helped me before as I am not the strongest when it comes to excel. I have had pivot table in my mind for a long time and haven't come close to my vision. Please find attached sheet with dummy data. I am trying to see how I can capture info in slicers /pivot or better visual format then a table:

Active Employee Data
New Hire for the month slicer
Term for the month slicer
Turn over data in graphs

I get asked certain types of reports a lot from my department and they want quick answers like what has been the turn over for month to month or yearly, how many hire we have had and all their info, what was terms in this month.... etc

Looking for a better way to capture my info. I would appreciate any help that can start me somewhere. Thanks.

Best regards,
Zeeworld

 

[GraH - Guido]

Hi,

Why do you have two tables?
In order to calculate churn, you need to know at which point in time you have active employees. That info is missing for the two terminated contracts.
Having this in a single table allows you to create DAX measures inside Power Pivot. You'd be needing a calendar table, but that is easily created in the Power Pivot window.
I'll maybe have a closer look later.

Data can be something like.

First Name	Last Name	Date of Hire	Date of Termination	Job Title	Supervisor	Cost Center	Work Location	Status	Salary
Alicia	Brown	3/04/2020​		Coordinator	Mendez, Maria	ABKJ	Montreal	Active	40.000​
Misti	Slides	9/03/2019​		Customer Service Rep	Brown, Hope	KJUN	San Fran	LOA	53.000​
Amin	Skorski	???	7/05/2021​	Manager	Mendez, Maria	ABKJ	Montreal	Term	40000​
Milan	Mehta	???	4/07/2020​	Lead	Brown, Hope	KJUN	San Fran	Term	53000​

 

[zeeworld]

Hi @GraH - Guido I thought i would need to separate the data in order to visualize new hire and terms separately but now I have made it one table. Please see attached.

Thanks

 

[GraH - Guido]

Hi zeeworld, didn't you upload the same file as before?

 

[GraH - Guido]

Hi again, would this do?

First Name	Last Name	Date of Hire	Date of Termination	Job Title	Supervisor	Cost Center	Work Location	Status	Salary	EmployeeID
Alicia	Brown	3/04/2020​		Coordinator	Mendez, Maria	ABKJ	Montreal	Active	40.000	7​
Misti	Slides	9/03/2019​		Customer Service Rep	Brown, Hope	KJUN	San Fran	LOA	53.000	3​
Amin	Skorski	10/11/2018​	7/05/2021​	Manager	Mendez, Maria	ABKJ	Montreal	Term	40.000	2​
Milan	Mehta	22/12/2017​	4/07/2020​	Lead	Brown, Hope	KJUN	San Fran	Term	53.000	1​
Brown	Milan	3/09/2020​		Coordinator	Mendez, Maria	ABKJ	Montreal	Active	40.000	8​
Slides	Alicia	9/03/2020​	10/04/2020​	Customer Service Rep	Brown, Hope	KJUN	San Fran	Term	53.000	6​
Skorski	Amin	10/11/2019​		Manager	Mendez, Maria	ABKJ	Montreal	Active	40.000	4​
Mehta	Misti	22/11/2019​		Lead	Brown, Hope	KJUN	San Fran	Active	53.000	5​

Please note, I'm not a specialist in DAX, like @Chihiro or @John Jairo V, so my approach may not be optimized.
You can also google "HR analytics via DAX" to find alternative solutions.

 

[zeeworld]

@GraH - Guido sorry didn't realize I uploaded the something. this definitely helps me start the spreadsheet thanks