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,
I have a tableof employees with Hire, Termination and Rehire Dates. I also have a date table
Im trying to get the average amount of active employees for whatever date range is selected on the date table. Should be the amount of active employees on the first date selected + active EEs of last day /2
Active Employee= Employee whos hire date is before selected date and termination date is blank or after selected date + employees with rehire date which is before selected date and status is Active
In Excel I have countifs formulas giving me the correct information
Can anyone help me out with getting this in BI?
Sample Data:
Company Name | Employee Number | Full Name | Default CC1 Description | Employee Status | Hire Date | Rehire Date | Termination Date |
ABC | 10611933 | Employee, o24 | Nursing | Active | 04/13/2009 | ||
ABC | 10611936 | Employee, o27 | Nursing | Active | 10/30/2009 | ||
ABC | 10611938 | Employee, o29 | Nursing | Active | 12/01/2010 | ||
ABC | 10611939 | Employee, o30 | Nursing | Terminated | 12/01/2010 | 01/31/2022 | |
ABC | 10611935 | Employee, o26 | Nursing | Active | 06/29/2012 | ||
ABC | 10611917 | Employee, o8 | Administration | Active | 07/14/2014 | ||
ABC | 10611918 | Employee, o9 | Administration | Active | 02/20/2017 | ||
ABC | 10611931 | Employee, o22 | Nursing | Active | 05/22/2017 | ||
ABC | 10611914 | Employee, o5 | Nursing | Active | 05/21/2018 | ||
ABC | 10611916 | Employee, o7 | Activities | Active | 07/15/2019 | ||
ABC | 10611910 | Employee, o1 | Activities | Terminated | 12/29/2020 | 06/11/2021 | |
ABC | 10611911 | Employee, o2 | Activities | Terminated | 12/29/2020 | 04/08/2021 | |
ABC | 10611912 | Employee, o3 | Activities | Terminated | 12/29/2020 | 07/12/2021 | |
ABC | 10611919 | Employee, o10 | Housekeeping | Active | 12/29/2020 | ||
ABC | 10611941 | Employee, o32 | Administration | Active | 02/24/2021 | 04/28/2022 | 03/27/2022 |
ABC | 10611920 | Employee, o11 | Housekeeping | Active | 03/24/2021 | ||
ABC | 10611921 | Employee, o12 | Housekeeping | Terminated | 05/05/2021 | 04/29/2022 | |
ABC | 10611934 | Employee, o25 | Administration | Active | 06/02/2021 | ||
ABC | 10611922 | Employee, o13 | Housekeeping | Terminated | 06/16/2021 | 08/20/2022 | |
ABC | 10611930 | Employee, o21 | Housekeeping | Terminated | 06/30/2021 | 07/16/2022 | |
ABC | 10611928 | Employee, o19 | Housekeeping | Terminated | 08/25/2021 | 03/21/2022 | 01/03/2022 |
ABC | 10611923 | Employee, o14 | Nursing | Terminated | 10/20/2021 | 03/29/2022 | |
ABC | 10611924 | Employee, o15 | Nursing | Terminated | 10/20/2021 | 06/11/2022 | |
ABC | 10611925 | Employee, o16 | Nursing | Terminated | 12/01/2021 | 03/27/2022 | |
ABC | 10611929 | Employee, o20 | Housekeeping | Terminated | 12/15/2021 | 12/28/2021 | |
ABC | 10611927 | Employee, o18 | Housekeeping | Active | 02/09/2022 | ||
ABC | 10611932 | Employee, o23 | Nursing | Terminated | 04/06/2022 | 05/20/2022 | |
ABC | 10611940 | Employee, o31 | Administration | Terminated | 06/15/2022 | 07/28/2022 |
Thanks in advance!
Solved! Go to Solution.
I created the measure below and it works for now! Thanks so much for all your help!!
Hi @MStark ,
You can create a measure as below to get it, please find the details in the attachment.
Active Employee =
VAR _seldate =
SELECTEDVALUE ( 'Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Employee'[Full Name] ),
FILTER (
'Employee',
(
'Employee'[Hire Date] <= _seldate
&& (
ISBLANK ( 'Employee'[Termination Date] )
|| 'Employee'[Termination Date] > _seldate
)
)
|| ( 'Employee'[Rehire Date] <= _seldate
&& 'Employee'[Employee Status] = "Active" )
)
)
Best Regards
Thanks for taking the time to look into this! I see it works in the test file you attached but when I copied the formula to the BI with real data, Im getting the same number for all months. Is there anything that needs to be updated?
Hi @MStark ,
Please try to update the formula of measure [Active Employee] as below and check if it can return the correct result...
Active Employee =
VAR _selyear =
SELECTEDVALUE ( 'Calendar'[Date].[Year] )
VAR _selmonth =
SELECTEDVALUE ( 'Calendar'[Date].[MonthNo] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'EE Info'[Full Name] ),
FILTER (
'EE Info',
(
VALUE (
YEAR ( 'EE Info'[Hire Date] )
& IF (
MONTH ( 'EE Info'[Hire Date] ) < 10,
"0" & MONTH ( 'EE Info'[Hire Date] ),
MONTH ( 'EE Info'[Hire Date] )
)
)
<= VALUE ( _selyear & IF ( _selmonth < 10, "0" & _selmonth, _selmonth ) )
&& (
ISBLANK ( 'EE Info'[Termination Date] )
|| VALUE (
YEAR ( 'EE Info'[Termination Date] )
& IF (
MONTH ( 'EE Info'[Termination Date] ) < 10,
"0" & MONTH ( 'EE Info'[Termination Date] ),
MONTH ( 'EE Info'[Termination Date] )
)
)
> VALUE ( _selyear & IF ( _selmonth < 10, "0" & _selmonth, _selmonth ) )
)
)
|| (
VALUE (
YEAR ( 'EE Info'[Rehire Date] )
& IF (
MONTH ( 'EE Info'[Rehire Date] ) < 10,
"0" & MONTH ( 'EE Info'[Rehire Date] ),
MONTH ( 'EE Info'[Rehire Date] )
)
)
<= VALUE ( _selyear & IF ( _selmonth < 10, "0" & _selmonth, _selmonth ) )
&& 'EE Info'[Employee Status] = "Active"
)
)
)
Best Regards
Still doesnt seem to work... does a relationship between the calendar and EE info table need to be active? I played around with measure Greg_Deckler provided and its working for now
Thanks so much for your time and assistance!
@MStark Maybe:
Measure =
VAR __Min = MIN('Dates'[Date])
VAR __Max = MAX('Dates'[Date])
VAR __Table = FILTER('Table',[Hire Date] <= __Min && ( [Termination Date] = BLANK() || [Termination Date] < __Min) )
VAR __Table1 = FILTER('Table', [Hire Date] <= __Max && ( [Termination Date] = BLANK() || NOT([Termination Date] > __Min && [Termination Date] < __Max) )
RETURN
DIVIDE(COUNTROWS(__Table) + COUNTROWS(__Table1),2)
@Greg_Deckler This seems to work though it doesnt include Rehires. Employee should also be counted as an active employee if has a rehire date before the selected date and status is Active or if terminated the termination date is after date selected
Thanks for your time!
@MStark Yeah, I considered rehires but I wasn't sure how that worked in your system. Does the employee get an additional row? Does the employee ID stay the same? If both of those are "yes" then you could potentially solve it using a SUMMARIZE and make sure you grab the MAX start date and then essentially lookup the corresponding termination date for each row. Messy.
@Greg_Deckler Employee # stays the same but an additional row is not added. Theres a separate column named Rehire Date (see sample data on original post)
I was thinking of adding 2 more Var for Rehires on first day selected and rehires on last day selected. Then to add it to the average. Did something like this but its not working. Think maybe the issue is that its counting employees who dont have a rehire date instead of only rehires <= Max or Min dates
Any advise on what Im doing wrong or a different way to get this done?
Thanks for taking the time to look into this!
@MStark Maybe tank your __RF and __RL variables and in your __F and __L variables where you refer to [Hire Date] replace that column reference with MAX([Hire Date, [Rehire Date])
I created the measure below and it works for now! Thanks so much for all your help!!
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |