Average Active EEs for Date Range

[MStark]

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!

[v-yiruan-msft]

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

[MStark]

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?

[v-yiruan-msft]

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

[MStark]

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!

[Greg_Deckler]

@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)

[MStark]

@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!

[Greg_Deckler]

@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.

[MStark]

@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!

[Greg_Deckler]

@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])

[MStark · Accepted solution]

I created the measure below and it works for now! Thanks so much for all your help!!