Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
primolee
Helper V
Helper V

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

 

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

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

 

v-kkf-msft_0-1614844558442.png

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.

View solution in original post

3 REPLIES 3
v-kkf-msft
Community Support
Community Support

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

 

v-kkf-msft_0-1614844558442.png

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.

Hello @v-kkf-msft 

 

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

 

Best regards,

David

amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.