Forum Discussion

tc97's avatar
tc97
Frequent Visitor
4 years ago
Solved

Measure: Rolling Active Users

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] ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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 () )
    )
)

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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's avatar
tc97
Frequent Visitor
4 years ago

Thanks so much! Exactly what I needed.