topic Re: Need PowerPivot (DAX) measure to determine number of active employees at any point in time in DAX Commands and Tips

Need PowerPivot (DAX) measure to determine number of active employees at any point in time

Anonymous — Thu, 05 Aug 2021 21:32:47 GMT

I'm able to create the calculation in Excel that returns the number of active employees for any given date. I'm hopeful that there is a way to do this dynamically in PowerPivot so I can return the accurate count regardless of filters.
The employee table contains the fields 'Hire Date', 'Employee Status', and 'Status Date' amongst others. So to get the number of active employees for any date the formula would be :
Generic: Active Employees = COUNTIFS([Hire Date], <= Selected Date , [Employee Status] = "Active") + COUNTIFS([Hire Date], <= Selected Date , [Employee Status] = "Terminated", [Status Date] >=Selected Date )

Actual formula:
=COUNTIFS(EmpData[[#All],[Hire Date]], "<="&R1, EmpData[[#All],[Employee Status]], "Active") + COUNTIFS(EmpData[[#All],[Hire Date]], "<="&R1, EmpData[[#All],[Employee Status Date]], ">="&R1, EmpData[[#All],[Employee Status]], "Terminated")

I've also used Sumproduct to get the same result.
Actual formula:
=SUMPRODUCT((EmpData[[#All],[Hire Date]]<=R1)*(EmpData[[#All],[Employee Status]]="Active")) + SUMPRODUCT((EmpData[[#All],[Hire Date]]<=R1)*(EmpData[[#All],[Employee Status]]="Terminiated")*(EmpData[[#All],[Employee Status Date]]>=R1))

To break that down, I want a count of the active employees that were hired on or before the selected date, plus the terminated employees that were hired before the comparison date, but not terminated until after the selected date. For the PowerPivot model there are two tables, 'Employee data' and 'Fiscal Calendar'. The selected date will be from the 'Fiscal Calendar'.
Any thoughts on how to create a measure that will yield the correct results in PowerPivot?

Re: Need PowerPivot (DAX) measure to determine number of active employees at any point in time

amitchandak — Fri, 06 Aug 2021 05:57:03 GMT

Anonymous , A measure like this assuming you getting a selected date from date table, not joined with any date

Active Employees = calculate(distinctCOUNT(EmpData[ID]) , filter(EmpData, (EmpData[Hire Date] >= max(Date[Date]) && [Employee Status] = "Active" ) && (
[Employee Status] = "Terminated" && EmpData[Hire Date] <= max(Date[Date]))))

refer if needed

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Re: Need PowerPivot (DAX) measure to determine number of active employees at any point in time

Jos_Woolley — Fri, 06 Aug 2021 06:40:36 GMT

Hi,

MyMeasure = VAR SelectedDate = SELECTEDVALUE ( 'Fiscal Calendar'[Date] ) RETURN 0 + CALCULATE ( COUNTROWS ( 'Employee data' ), FILTER ( 'Employee data', 'Employee data'[Employee Status] = "Active" && 'Employee data'[Hire Date] <= SelectedDate ) ) + CALCULATE ( COUNTROWS ( 'Employee data' ), FILTER ( 'Employee data', 'Employee data'[Employee Status] = "Terminated" && 'Employee data'[Hire Date] <= SelectedDate && 'Employee data'[Employee Status Date] >= SelectedDate ) )

Regards

Re: Need PowerPivot (DAX) measure to determine number of active employees at any point in time

Anonymous — Fri, 06 Aug 2021 13:11:35 GMT

Thank you for your prompt response. However, I'm not able to use this approach as SELECTEDVALUE is not an option as a function within Excel/PowerPivot.

Any other thoughts?

Thank you.

Re: Need PowerPivot (DAX) measure to determine number of active employees at any point in time

Anonymous — Fri, 06 Aug 2021 16:51:47 GMT

amitchandak,

Thank you for your response. The solution you suggested is an approach that I had tried previously. However, in PowerPivot it only returns the number for the dates in the intersection, not the total number of employees (i.e. for May it should return 15, but only returns 2, the two that had activity in that period).

I was able to get the result I was looking for using my data in PowerBI using the suggestions from your linked referece. However, when I translate that to PowerPivot it does not work correctly.

Thank you again.

Re: Need PowerPivot (DAX) measure to determine number of active employees at any point in time

Jos_Woolley — Fri, 06 Aug 2021 19:18:17 GMT

Anonymous

Apologies, I overlooked that this was for Power Pivot. Try:

MyMeasure := 0 + CALCULATE ( COUNTROWS ( 'Employee data' ), FILTER ( 'Employee data', 'Employee data'[Employee Status] = "Active" && 'Employee data'[Hire Date] <= [Selected Date] ) ) + CALCULATE ( COUNTROWS ( 'Employee data' ), FILTER ( 'Employee data', 'Employee data'[Employee Status] = "Terminated" && 'Employee data'[Hire Date] <= [Selected Date] && [Employee Status Date] >= [Selected Date] ) )

Replace [Selected Date] as meets your requirements.

Regards