Measure to calculate active employees for multiple years based on hire date and termination date

[powerbiss]

Hi Team,
I am trying to create a measure to show active employees for 2018, 2019, 2020, 2021, and 2022 based on hire date and termination date. I will have a slicer for the years.

Active employees for 2018 = 8
Active employees for 2019 = 8
Active employees for 2020 = 8
Active employees for 2021 = 10
Active employees for 2022 = 15

Thanks for all your help.

[DataInsights · Accepted solution]

@powerbiss,

This solution uses a disconnected Years table (one row per year).

Measure:

Active Employees = 
VAR vCurYear =
    SELECTEDVALUE ( Years[Year] )
VAR vResult =
    SUMX (
        Table1,
        VAR vHireYear =
            YEAR ( Table1[date_of_hire] )
        VAR vTermYear =
            IF (
                ISBLANK ( Table1[date_of_termination] ),
                9999,
                YEAR ( Table1[date_of_termination] )
            )
        RETURN
            IF ( vCurYear >= vHireYear && vCurYear <= vTermYear, 1 )
    )
RETURN
    vResult

Use the Years[Year] column in a visual. If you want the Year slicer to be based on your date table, the solution below shows how to achieve this. The concept is to create a clone of the date table, and use DAX to control the visual filtering.

https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/ 

[powerbiss]

Hello Sir,

Really appreciate your help in solving my problem. The DAX measure worked great.

Again thanks for your help

[DataInsights]

Glad to hear that worked!