Forum Discussion

primolee's avatar
primolee
Icon for Helper V rankHelper V
5 years ago
Solved

Active and resigned employee count between a date range

Hello everyone,

 

I have a table of active employees of each month like the following.

DateEmployee
2020/12/1David
2020/12/1Jeff
2020/12/1Marco
2020/12/1Joy
2020/12/1Peter
2021/1/1David
2021/1/1Jeff
2021/1/1Marco
2021/1/1Joy
2021/1/1Jenny
2021/1/1Kate
2021/2/1Jeff
2021/2/1Marco
2021/2/1Joy
2021/2/1Jenny
2021/2/1Kate
2021/2/1Aaron

 

From 2020/12 to 2021/1, 1 person Peter left, and 2 people Jenny and Kate joined.  From 2021/1 to 2021/2, David left and Aaron joined.

 

But if we were to look from 2020/12 to 2021/2, Peter and David left, and Jenny, Kate and Aaron joined.

 

I want to be able to show how many people joined and how many people left, and also the turnover rate of a date range.

 

I am sorry but I cannot think of a good way to do this.  Need some help on this, thank you!

 

Best regards,

David

 

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi  primolee,

    Try the following formula to create measures:

    Left = 
var _First = MIN('Table'[Date])
var _Last = MAX('Table'[Date])
var Table1 = 
    SELECTCOLUMNS(
        FILTER(
            'Table',
            'Table'[Date].[MonthNo] = MONTH(_First)
            &&'Table'[Date].[Year] = YEAR(_First)
        ),
        "Employee",
        'Table'[Employee]
    )
var Table2 = 
    SELECTCOLUMNS(
        FILTER(
            'Table',
            'Table'[Date].[MonthNo] = MONTH(_Last)
            &&'Table'[Date].[Year] = YEAR(_Last)
        ),
        "Employee",
        'Table'[Employee]
    )
return 
    COUNTAX(
        EXCEPT(Table1,Table2),
        [Employee]
)
    Joined = 
var _First = MIN('Table'[Date])
var _Last = MAX('Table'[Date])
var Table1 = 
    SELECTCOLUMNS(
        FILTER(
            'Table',
            'Table'[Date].[MonthNo] = MONTH(_First)
            &&'Table'[Date].[Year] = YEAR(_First)
        ),
        "Employee",
        'Table'[Employee]
    )
var Table2 = 
    SELECTCOLUMNS(
        FILTER(
            'Table',
            'Table'[Date].[MonthNo] = MONTH(_Last)
            &&'Table'[Date].[Year] = YEAR(_Last)
        ),
        "Employee",
        'Table'[Employee]
    )
return 
    COUNTAX(
        EXCEPT(Table2,Table1),
        [Employee]
)

     

    This is my PBIX file.

    https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-lazhang_microsoft_com/EdLs_JfXa4tGl9qGNyvgcyUBgJ72QzJnuqYHJUeiOd3-4Q?e=Isx7Ts

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies