Hi, I have a tableof employees with Hire, Termination and Rehire Dates. I also have a date tableIm 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 /2Active 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 ActiveIn Excel I have countifs formulas giving me the correct informationCan anyone help me out with getting this in BI? Sample Data:Company NameEmployee NumberFull NameDefault CC1 DescriptionEmployee StatusHire DateRehire DateTermination DateABC10611933Employee, o24NursingActive04/13/2009 ABC10611936Employee, o27NursingActive10/30/2009 ABC10611938Employee, o29NursingActive12/01/2010 ABC10611939Employee, o30NursingTerminated12/01/2010 01/31/2022ABC10611935Employee, o26NursingActive06/29/2012 ABC10611917Employee, o8AdministrationActive07/14/2014 ABC10611918Employee, o9AdministrationActive02/20/2017 ABC10611931Employee, o22NursingActive05/22/2017 ABC10611914Employee, o5NursingActive05/21/2018 ABC10611916Employee, o7ActivitiesActive07/15/2019 ABC10611910Employee, o1ActivitiesTerminated12/29/2020 06/11/2021ABC10611911Employee, o2ActivitiesTerminated12/29/2020 04/08/2021ABC10611912Employee, o3ActivitiesTerminated12/29/2020 07/12/2021ABC10611919Employee, o10HousekeepingActive12/29/2020 ABC10611941Employee, o32AdministrationActive02/24/202104/28/202203/27/2022ABC10611920Employee, o11HousekeepingActive03/24/2021 ABC10611921Employee, o12HousekeepingTerminated05/05/2021 04/29/2022ABC10611934Employee, o25AdministrationActive06/02/2021 ABC10611922Employee, o13HousekeepingTerminated06/16/2021 08/20/2022ABC10611930Employee, o21HousekeepingTerminated06/30/2021 07/16/2022ABC10611928Employee, o19HousekeepingTerminated08/25/202103/21/202201/03/2022ABC10611923Employee, o14NursingTerminated10/20/2021 03/29/2022ABC10611924Employee, o15NursingTerminated10/20/2021 06/11/2022ABC10611925Employee, o16NursingTerminated12/01/2021 03/27/2022ABC10611929Employee, o20HousekeepingTerminated12/15/2021 12/28/2021ABC10611927Employee, o18HousekeepingActive02/09/2022 ABC10611932Employee, o23NursingTerminated04/06/2022 05/20/2022ABC10611940Employee, o31AdministrationTerminated06/15/2022 07/28/2022 Thanks in advance!

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

Helper III

3 years ago

Solved

Average Active EEs for Date Range

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!

Need Help

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

10 Replies

Greg_Deckler
Community Champion
3 years ago
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
  Helper III
  3 years ago
  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
    Community Champion
    3 years ago
    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.

Anonymous

Not applicable

3 years ago

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

Average Active EEs for Date Range.pbix107 KB

MStark

Helper III

3 years ago

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?

Anonymous

Not applicable

3 years ago

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

Average Active EEs for Date Range(Updated).pbix112 KB

Forum Discussion

Average Active EEs for Date Range

10 Replies

Related content

Creating ICMs via Activator

Activity Statistics

Fabric pipeline activities web activity Oauth error

Average and Moving Average

Weekly Average