Measure: Rolling Active Users

Question

Hi,&nbsp;&nbsp;I have a data table tracking employees internal moves between departments.&nbsp;&nbsp;&nbsp;NameDepartmentStart DateEnd Date[Employee Name][Department Name][First Day in Department][Last Day in Department - NULL IF ACTIVE]&nbsp;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.&nbsp;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.&nbsp;&nbsp;Is this something that's easily achievable?&nbsp;Thanks,Toby&nbsp;

jihwan_kim · Accepted Answer

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.
&nbsp;
&nbsp;
Employees count measure: =
CALCULATE (
    COUNTROWS ( VALUES ( Table[Name] ) ),
    FILTER (
        Table,
        Table[Start Date] &lt;= MAX ( Calendar[Date] )
            &amp;&amp; OR ( Table[End Date] &gt;= MIN ( Calendar[Date] ), Table[End Date] = BLANK () )
    )
)

&nbsp;

Forum Discussion

Measure: Rolling Active Users

2 Replies

Related content

Rolling accumulation

Rolling 12M Average measure

Rolling accumulation

Creating ICMs via Activator

Activity Statistics

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