Log In
Register
Home
Active Member
August 30, 2019
Offline
Mynda, you are AMAZING!!! I have gone through the HR Dashboard webinar several times and I am trying to replicate what you have done with our company data. The data goes back to 1968. We have 473 active employees. I created the Measurements and the PivotChart. I only want to view the past 4 years with the total headcount. When I select the past 4 years, the numbers only show for those years. What formula would I need to show the active employees with the new hires?
1st - Employee Count GOOD
=CALCULATE(COUNT([EmpID]),FILTER(ALL('HRDashboard'[Date]),[Date]=MAX([Date])))
2nd - Active Employees - GOOD
=CALCULATE([EmpCount],FILTER('HRDashboard',ISBLANK('HRDashboard'[TermDate])))
3rd - New Hires - GOOD
=CALCULATE(COUNT([EmpID]),FILTER('HRDashboard','HRDashboard'[isNewHire]="Yes"))
ACTIVE EMPLOYEES
Tenure Days = =DAYS(TODAY(),F10) OR =DATEDIF(F4,TODAY(),"d")
Tenure Months = =DATEDIF(F3,I3,"M") OR =DATEDIF(F4,TODAY(),"m")
Tenure Years = =DAYS(TODAY(),F10)/365.25 OR =DATEDIF(F4,TODAY(),"y")
TERMED EMPLOYEES
Tenure Days = =DATEDIF(F2,I2,"D")
Tenure Months = =DATEDIF(F2,I2,"M")
Tenure Years = =DATEDIF(F2,I2,"Y")
Regards,
Mike
July 16, 2010
Online
Hi Mike,
It's great to hear you've been able to use the techniques in your own work.
I'm confused about your question because the top formulas are DAX measures whereas the bottom formulas are Excel, so I'm not sure what you're trying to do or where you're trying to calculate the figures. Can you please upload a sample file with some anonymised data?
Mynda
Active Member
August 30, 2019
Offline
The Excel formulas were to make sure the data would be linked. The DAX is what I need help with. The PivotChart for Active Employees is not readable because it is too long and overlaps. When I select the last 4 years, only those employees are counted. I want to have all employees counted while only showing the last 4 years. My goal is to break down each location to see trends.
Mike
July 16, 2010
Online
Hi Mike,
Thanks for sharing your file. If you look at my file you'll see the PivotTable on the Actives sheet uses the Date field, which is the snapshot date, not the Hire Date.
Your file only has one snapshot date; July 31, 2019. Your chart shows the new hires and active employees over hire dates, but it can't also show the total number of employees at a point in time because there is only one point in time, i.e. July 31, 2019.
BTW, the isNewHire column has some erroneous data. In my file they're only a new hire in the first month they joined. If you look at your file, row 3 for Jerry Barrera has a hire date of Aug 29, 2016 and has 'Yes' in the isNewHire column for the data captured at July 31, 2019. i.e. in my file they're only a new hire when the Date and Hire Dates are for the same month and year.
The measures are designed to work with data captured at monthly intervals (i.e. the 'Date' column) and then analysed using the Date column. For now you can only look at the data as at July 2019.
Note: You also have duplicate employees, e.g. emp ID 1371 is in the table twice. As is 1366, to name just two. There are others.
I hope that points you in the right direction.
Mynda
Active Member
August 30, 2019
Offline
Wow! I have some cleanup to do on the file. The DAKS for employee count should read:
=CALCULATE(COUNT([EmpID]),FILTER(ALL('HRDashboard'[HireDate]),[HireDate]=MAX([HireDate])))
I am looking forward to completing this dashboard. I am using Excel 2019.
You are a wizard with Excel!!!
Mike
1 Guest(s)
