HR Dashboard | Power Pivot | Excel Forum • My Online Training Hub

• Skip to main content
• Skip to header right navigation
• Skip to site footer

HR Dashboard|Power Pivot|Excel Forum|My Online Training HubYou are here: Home / HR Dashboard|Power Pivot|Excel Forum|My Online Training Hub Log In Register Select ForumForum Rules and GuidesGeneral Excel Questions & AnswersDashboards & ChartsVBA & MacrosPower QueryPower PivotExcel DashboardsPower QueryPower PivotXtreme Pivot TablesExcel for Decision MakingExcel for FinancePower BIExcelWordOutlookPowerPointExcel ExpertExcel for Customer Service ProfessionalsExcel Analysis ToolpakExcel TablesExcel for Operations ManagementFinancial ModellingAdvanced Excel FormulasPivot Tables Quick Start SearchAdvanced Search|Last Search ResultsSearchForum ScopeCurrent forumAll forumsInclude blog postsMatchMatch any wordMatch all wordsMatch phraseForum OptionsPosts onlyTopic titles onlyPosts and topic titlesMinimum search word length is 3 characters - maximum search word length is 84 characters Search Login name Password Remember me Lost password? HomeExcel ForumPower PivotHR Dashboard HR DashboardMike ArnoldActive MemberMembers Forum Posts: 3Member Since: August 30, 2019 Offline1August 30, 2019 - 8:13 am

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

Mynda TreacyAdmin Forum Posts: 4784Member Since: July 16, 2010 Offline2August 30, 2019 - 5:00 pm

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

Mike ArnoldActive MemberMembers Forum Posts: 3Member Since: August 30, 2019 Offline3August 30, 2019 - 10:51 pm

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

Mynda TreacyAdmin Forum Posts: 4784Member Since: July 16, 2010 Offline4August 31, 2019 - 4:23 pm

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

Mike ArnoldActive MemberMembers Forum Posts: 3Member Since: August 30, 2019 Offline5August 31, 2019 - 11:34 pm

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

Forum Timezone: Australia/BrisbaneMost Users Ever Online: 245Currently Online: Guest(s) 7Currently Browsing this Page: 1 Guest(s)Top Posters: Catalin Bombea: 1945SunnyKow: 1432Anders Sehlstedt: 959Purfleet: 415Hans Hallebeek: 377Frans Visser: 351David_Ng: 318lea cohen: 248Jessica Stewart: 219A.Maurizio: 216Newest Members: Jennifer Hullkris kingHasanul KarimTerrie sJack EnnisGary DayMichael WiliamsJim CollierAllan ChanRik LelliottForum Stats: Groups: 3Forums: 25Topics: 7159Posts: 31414Member Stats: Guest Posters: 50Members: 34674Moderators: 2Admins: 3Administrators: Mynda Treacy, Philip Treacy, JessicaModerators: Velouria, Riny van Eekelen© Simple:Press — New / Updated TopicsADDING MONTHSequence functionTool-tip like CommentsISSUE General Ledger using Pivot TablePersonal.xlsb file opening like a normal…Automated Order From from various sheets…[Ultimate Personal Budget] Inf…Auto update - between two pages excelUse date ranges in pivot tableExcel and Power Point for Accounting and…Linking Data in SharePoint EnvironmentData sorting issue (table vs dashboard)My macro isn't running when I edit a pag…Upgrade VBA code to remove all " - Copy.…Power Query - Spotify Streaming DataPower Query extremely slow to refreshPowerQuery - SharePoint (https)Power Query will not startPDF imported to power queryBest way to query a MySQL database

Sidebar

Download A Free Copy of 100 Excel Tips & Tricks

Download My eBook

We respect your privacy. We won’t spam you.

x 0