cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hchatter
Frequent Visitor

Measures for calculating Staff Turnover are returning incorrect totals

I have been given a file of current employees that has start dates and another file of terminated employees that has term dates but no start dates. Because this is prototype to prepare us for reviewing this kind of data going forward, I inserted a dummy start dates for the terminated employees (for testing purposes). I combined the current employees and terminated employees into one file as was done in the instructional  youtube video: Calculating Staff Turnover In Power BI Using DAX - HR Insights 

I then used the DAX expressions I copied from that video.

 

However, I am not getting the correct totals and have no idea what I've done wrong or how to fix it.

 

See details below:

***The following measure returns a total of 2,613 for current staff (ironically this is the total of the terms) but should be 11,080.

 

Current Staff =

CALCULATE( COUNTROWS( 'Staff Population' ),

                FILTER( VALUES('Staff Population'[Start Date]), 'Staff Population'[Start Date] <= MAX( Dates[Date] ) ),

                FILTER( Values('Staff Population'[Term Date] ), OR( 'Staff Population'[Term Date] >=MIN( Dates[Date] ), ISBLANK('Staff Population'[Term Date] ) ) ) )

 

Below image of the 'Current Staff' measure results:

CurrentStaff.PNG

***The following measure returns a total of 2 terminated employees but there should be a total of 2,613 Terminations.

Staff Turnover =

CALCULATE( COUNTROWS( 'Staff Population' ),

                FILTER( VALUES('Staff Population'[Term Date] ), 'Staff Population'[Term Date] <= MIN( Dates[Date] ) ),

                                'Staff Population'[Term Date] <> BLANK() )

 

Below image of the 'Staff Trunover' measure results:StaffTurnover.PNG

Below is the model relationship view

RelationshipModel.PNG

Below is the Data Table View

Data Table ViewData Table View

 
 

I have a pbix file I can share (it's all redacted data) but I can't upload it here.

 

Also, please let me know if you know of good (but inexpensive) Power BI desktop courses if you know of any. 

2 ACCEPTED SOLUTIONS
amitchandak
Super User IV
Super User IV

@hchatter , refer to my HR blogs and the comments. There is small change required for < /= , was causing some issue

 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@amitchandak, Wow! This is awesome! Thank you for pointing me to your HR blog! So helpful!!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@hchatter , refer to my HR blogs and the comments. There is small change required for < /= , was causing some issue

 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@amitchandak, Wow! This is awesome! Thank you for pointing me to your HR blog! So helpful!!

View solution in original post

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.