Active and resigned employee count between a date range

[primolee]

Hello everyone,

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

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

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

[v-kkf-msft · Accepted solution]

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_JfXa4tGl9qGNyvgcy...

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.

[primolee]

Hello @v-kkf-msft 

Just studied your codes, this is amazing!  Thank you so much!

Best regards,

David

[amitchandak]

@primolee , is this like a snapshot table?

Check the approach of Month on Month using isblank for Customer Retention, that should help

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...