Measure to count active employees in a period based on Slicer Calendar

[Beavertron]

Hi,

I have a table which contains employees DateStarted and Leavedates. Leavedates is NULL if they are still active. In SQL I would run a where statement based on SSRS date parameters to only include active in a month period for example,

where (LeaveDate >= @datefrom and DateStarted < @dateto) or (DateStarted < @dateto and LeaveDate is NULL)

so if I replace the parameters with dates

where (LeaveDate >= '01 May 2020' and DateStarted < '01 Jun 2020) or (DateStarted < '01 Jun 2020) and LeaveDate is NULL)

... so this captures anyone who were active for any portion of this month for example, or even start before and finish after etc.

I am struggling to convert this to a measure so I can display a count of employees on a 3D card, based on a date slider linked to a calendar table, making this value dynamic. Thank you in advance.

[Fowmy · Accepted solution]

@Beavertron 

Use the following measure to get the active employees to count. Modify the table name and field names as per your model.

Employee Count = 
VAR __DATE = MAX ( 'Date'[Date] )
RETURN
    SUMX (
        EmployeeTable,
        IF (
            EmployeeTable[DateStarted] <= __DATE
                && OR ( EmployeeTable[Leavedates] >= __DATE , ISBLANK(EmployeeTable[Leavedates]) ),
            1,
            BLANK ()
        )
    )