CODEWITHSUNDEEP
powerbidaxssas-tabular
Ariox66
Ariox66

Reputation: 660

Calculating the number of active employees as at previous months with DAX

I am wondering if there's a way to calculate the number of active employees with the use of start date/ termination date without having to create a daily snapshot table.

the employee table is like this:

Emp_ID.   Start Date        Termination Date      other columns

and obviously Dim_date is as usual:

Date_key      Date            Year.    Month.    Etc...

Employee table is connected to Dim_Date via Start Date field, so if we use a DAX formula like :

ActiveEmps =
CALCULATE (
COUNTX (
    FILTER (
        employee,
        [start date] < MIN ( Dim_Date[Date] )
            && [Termination Date] > MAX ( Dim_Date[Date] )
    ),
    Emp_ID
) 
,other filters within calculate context)

It won't work and the reason is it's only counting active employees in which their start date falls within the selected time period. (because of the relationship)

Upvotes: 0

Views: 4610

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

I would have done it this way:

ActiveEmps :=
VAR ThisDate =
    MIN( Dim_Date[Date] )
RETURN
    CALCULATE(
        COUNTROWS( employee ),
        FILTER(
            ALL( employee ),
            employee[Start Date] < ThisDate
                && employee[Termination Date] > ThisDate
        )
    )

Upvotes: 1

Related Questions