Forum Discussion

rdonohoe's avatar
rdonohoe
Regular Visitor
8 years ago
Solved

Active Employees

Hi,

 

I have a table employees with two relevant fields start and end. 

 

I have a line chart showing the numbers of new and leaving employees per month over a year. I am looking to add a thind line showing the number of employees employed on the last day of each of those months. 

 

Anyone help me with this?

  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    rdonohoe,

     

    You may refer to the following measure.

    Measure =
VAR d =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table1[employeeId] ),
        FILTER ( Table1, Table1[start] = d )
    )
        + 0

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Create a table with the end dates for each month. Add a calculated column where you COUNT or COUNTROWS the number of rows that have an end date less than that month. Something along those lines should work.

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    8 years ago

    rdonohoe,

     

    You may refer to the following measure.

    Measure =
VAR d =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table1[employeeId] ),
        FILTER ( Table1, Table1[start] = d )
    )
        + 0