Calculate active employees for multiple years

[powerbilead]

Hi All,
Can I get some help in calculating number of active employees for each year based on hire date and term date. I have attached sample workbook with some data

Thanks in advance

 

[Chihiro]

Since you posted in PQ/PP forum. I'm assuming that you want this in DAX.

General construct. Assuming that you have calendar table.

This assumes that active employee is those employee active at end of year. And Pivot has year as row field.

NumEmp :=
VAR curDate =
    MAX ( DimDate[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( empdata[emp_id] ),
        empdata[date_of_hire] <= curDate,
        OR ( ISBLANK ( empdata[date_of_term] ), empdata[date_of_term] > curDate )
    )

Exact calculation will depend on what you consider to be active employee in a given year.

 

[powerbilead]

Since you posted in PQ/PP forum. I'm assuming that you want this in DAX.

General construct. Assuming that you have calendar table.

This assumes that active employee is those employee active at end of year. And Pivot has year as row field.

NumEmp :=
VAR curDate =
    MAX ( DimDate[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( empdata[emp_id] ),
        empdata[date_of_hire] <= curDate,
        OR ( ISBLANK ( empdata[date_of_term] ), empdata[date_of_term] > curDate )
    )

Exact calculation will depend on what you consider to be active employee in a given year.

Thank you Sir. Yes I do have a calendar table. Much appreciated for your help in developing a DAX formula. Thanks again