Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adj87
Helper I
Helper I

Historical Headcount with Mutliple Rehires

Hi Everyone,

I'm running into an issue building an attrition dashboard. I am using an existing PeopleSoft query which contains every job change for our employees. I've decided to use this because it has the Hire, Rehire and Term rows. It also has the detail required for upper management, the employee information at the time: Location, Department, Job Title. This is required for added accuracy for attrition reporting.

 

I have seen several solutions to attrition: v-kalyj-msft had a really good one, but it only considered Hires/Terms. DNA Enterprise had another good one, but didn't factor in the historical data.

 

My inital measure was a combination of HIR, REH and TER codes. These only account for employees with these action codes in the current year. This only shows 614 when there are ~4k.

CALCULATE(
    COUNTROWS(JobHistory),
        FILTER(JobHistory,JobHistory[Action]="HIR")
)

 

My goal is to be able to show the total Active Heads and YTD Terms for any given Year-Month. The continual issue (and maybe I'm overcomplicating this?) are the employees we have rehired multiple times with more than 1 term date. Does this matter, does it need to be accounted for?

 

I have tried a few other measures, but all have resulted in errors. I have the same measure for [TermDate]. [Heads] results in a blank. I am assuming this is due to my misuse of measures.

 

HireDate = CALCULATE(
    MIN(JobHistory[JH Eff Date]),
        JobHistory[Action]="HIR"
        )

Heads = COUNTX (
    FILTER (
        ALL (JobHistory ),
        ISERROR([TermDate])
            && [HireDate]
                < MINX (
                    FILTER (
                        ALL ( 'Date' ),
                        'Date'[FW YearMonthNumber] = SELECTEDVALUE ( 'Date'[FW YearMonthNumber])
                    ),
                    'Date'[Date]
                )
    ),
    JobHistory[JH EID]
)

 

TurnoverDashboard 

 

Thank you for your help,

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@adj87 , Please use this approach of inactive join with both dates(if they are in one table) , you can also use distinctcount in that case . The file is attached to similar data, not your data 

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Share some simplistic data, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

What I can share of my data is linked below the model "TurnoverDashboard"

 

I believe I was overcomplicating my problem, I just need to either find a way to get my data into this format:

 

IDStartEnd
11/1/20202/1/2020
16/1/2020 
21/1/2020 
31/1/2020 
45/1/20205/1/2020
51/1/20206/1/2020
52/1/20214/1/2022
512/1/2022 

 

Or rebuild the source query.

 

This format should allow me to get the attrition numbers I need

Hi,

You have shown your target table.  Please also show the source data table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

After a few hours of tinkering, I found a solution which allows me to finish building the dashboard. I can revisit the efficiency issues later with our IT team

amitchandak
Super User
Super User

@adj87 , Please use this approach of inactive join with both dates(if they are in one table) , you can also use distinctcount in that case . The file is attached to similar data, not your data 

Thank you for following up I added your suggestion, and was able to create accurate heads for Hire/Term. Based on my data source, I am unable to create an accurate table with : ID, Start Date, End Date since I just have a list of employment changes (I tried to pivot the data, but was never able to get close to a good solution).

 

You mentioned the dates (start/end) could be in seperate tables, so that is what I am currently persuing. I have adjusted a few measures, but I am stuck on "Current Employees" . I have a bridge table between my Hires-Terms tables, but this doesn't allow for the use of TermDate (I'm assuming its a directional issue?).

 

Screenshot 2022-12-09 160100.png

Is it possible to account for the seperate tables using DAX (either with a measure or just combine the seperate dates into a new table and preserving the proper flow of events for a rehire with multiple rows) or is this a scenario where its easier to revisit the data source.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.