Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]
)
Thank you for your help,
Solved! Go to Solution.
@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
Hi,
Share some simplistic data, explain the question and show the expected result.
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:
ID | Start | End |
1 | 1/1/2020 | 2/1/2020 |
1 | 6/1/2020 | |
2 | 1/1/2020 | |
3 | 1/1/2020 | |
4 | 5/1/2020 | 5/1/2020 |
5 | 1/1/2020 | 6/1/2020 |
5 | 2/1/2021 | 4/1/2022 |
5 | 12/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.
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
@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?).
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |