Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone-
I am trying to solve for an issue and have worked at it for way too long, need to finally ask for help. I am working on turnover metrics which requires Average Active employees for a denomenator, and number of terminations as a numerator. Terminations is not a problem. Equation works when I include ALL employees, but if I want to show turnover by department, my denomenator does not update and so my % is way off.
I am dealing with two tables. Table 1 is a DimDate table joined, 1 to Many, to Table 2, a demographic table with dates related to employees.
Goal: Show an average count of active employees in a timeframe, and have this number react to filters for department.
Current state: I can show the average count of active employees in a timeframe, but the number does not react to the filters on the page/on the visual for department. I have built my formula as a column in my Date Term table and then use a Measure for my visual which is impacted by filters for date ranges.
Current formula "count unique IDs as active if their hire date is before this date and they are either still active or their term date was after this day":
Active Employees =
CALCULATE (DISTINCTCOUNT ( Employment[EmployeeNumber] ),
FILTER (Employment, Employment[LastHire] <= 'Date Term'[Date]
&& OR (Employment[TerminationDate] > 'Date Term'[Date],ISBLANK ( Employment[TerminationDate] ) = TRUE ())))
Measure- Average Active Employees = CALCULATE(average('Date Term'[Active Employees]))
Ex Table 1: 'Date Term'
Date | FY | FY-Quarter | FY-Period | Active Employees (calc above) |
5/31/2019 | 2019 | 2019-4 | 2019-12 | 1100 |
6/1/2019 | 2020 | 2020-1 | 2020-1 | 1101 |
Ex Table 2: 'Employment'
EmloyeeNumber | LastHire | TerminationDate | Department |
111 | 1/1/2017 | Finance | |
222 | 2/1/2005 | 8/1/2019 | Legal |
Hi ehmacc,
If possible, could you please inform me more detailed information(such as your sample data and your expected output)? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Links below. Easiest example would be using even just the emplyee count today, or the average this period. All company should be about 1025. But if I filtered it to Finance, I would expect 82, or 15 for HR.
As mentioned before- The Measure for Terminations as part of my Turnover calcultaion works and interacts with filters for Department and the Period/Quarter just fine;
Employment- https://drive.google.com/file/d/1ehwNoLXvNRpUu2DJuaKxp1q_sX-0g6Mt/view?usp=sharing
DimDate- https://drive.google.com/file/d/16mYPQ8nbK7JLlp3cpPqwgmpqdIPsPVA5/view?usp=sharing
Apologies for using Google Drive. My organization has our One Drive as internally shareable only. I also had to format a column on my DimDate query once I loaded to make the join work for both dates to have the same fomat-
Hi ehmacc,
Did you mean you want to get interactive based on date range slicer? If so, you could try below measure
Measure =
VAR maxd =
CALCULATE ( MAX ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
VAR mind =
CALCULATE ( MIN ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
RETURN
CALCULATE (
COUNT ( Employment[Department] ),
FILTER (
Employment,
Employment[Termination Date] < maxd
&& Employment[Termination Date] > mind
|| Employment[Termination Date] = BLANK ()
)
)
If this is not what you want , please correct me and inform em your expected output.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That equation only returns the count of employees with termination dates in that range, it isn't picking up employees without a termination date, which I need included.
I also had to change the filter to include Hire date as the first piece- it should filter for employees who were Hired before the last day of the date range who either had a term date greater than the first day of the date range or no term date. But this has not fixed the count problem- it is still only counting if they have a termination date.
Measure =
VAR maxd =
CALCULATE ( MAX ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
VAR mind =
CALCULATE ( MIN ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
RETURN
CALCULATE (
COUNT ( Employment[Department] ),
FILTER (
Employment,
Employment[Hire Date] < maxd
&& Employment[Termination Date] > mind
|| Employment[Termination Date] = BLANK ()
)
)
Can you calculate Avg Employee like
Total employee/Total Month.
That my works better
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |