Measure: Rolling Active Users

[tc97]

Hi, 

I have a data table tracking employees internal moves between departments. 

Name	Department	Start Date	End Date
[Employee Name]	[Department Name]	[First Day in Department]	[Last Day in Department - NULL IF ACTIVE]

I'm now trying to create a measure to give me the average number of people working in a department at any given date (so that it'll work at date, month, and year level). Essentially this should just be a count of any record where the start date is less than or equal to the date, AND the end date is more than the date OR null.

I have a calendar table, but haven't created a relationship with this one yet as I'm unsure how to do this as I want to consider both Date columns in the calculation. 

Is this something that's easily achievable?

Thanks,

Toby

[Jihwan_Kim · Accepted solution]

Hi,

Please try the below measure.

Create a table visualization, and add one of columns from a calendar table (year, month, week, or date column).

And then add the below measure into the table visualization.

Employees count measure: =
CALCULATE (
    COUNTROWS ( VALUES ( Table[Name] ) ),
    FILTER (
        Table,
        Table[Start Date] <= MAX ( Calendar[Date] )
            && OR ( Table[End Date] >= MIN ( Calendar[Date] ), Table[End Date] = BLANK () )
    )
)

[tc97]

Thanks so much! Exactly what I needed.