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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ma142
Frequent Visitor

How to get the new records and dropped records?

Hello, 

 

I have a monthly report that shows the employees information. Report has entry date and terminated date. How do I get the new hired and terminated employees in each month?

 

Thank you

 

Mildred

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

Hi  @ma142 ,

You can refer the following links to get it:

Get the number of starters and leavers:

Starters and leavers from monthly extract

New Starters =
VAR lastmonth =
    CALCULATETABLE (
        VALUES ( Table1[Employee ID] ),
        FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 )
    )
RETURN
    IF (
        MAX ( Table1[MonthNo] ) = 1,
        CALCULATE ( COUNT ( Table1[Employee ID] ) ),
        CALCULATE (
            COUNT ( Table1[Employee ID] ),
            FILTER ( Table1, NOT ( Table1[Employee ID] ) IN lastmonth )
        )
    )
Leavers =
VAR lastmonth =
    CALCULATETABLE (
        VALUES ( Table1[Employee ID] ),
        FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 )
    )
RETURN
    IF (
        MAX ( Table1[MonthNo] ) = 1,
        0,
        CALCULATE (
            COUNT ( Table1[Employee ID] ),
            FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 )
        )
            - CALCULATE (
                COUNT ( Table1[Employee ID] ),
                FILTER ( Table1, Table1[Employee ID] IN lastmonth )
            )
    )

How to calculate Starters and Leavers |DatesinPeriod |CountRows|

 

Get the running total of starters and leavers:

Starters/leavers - cumulative

Measure =
CALCULATE (
    SUM ( 'Table'[Hours] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Start Date] <= MAX ( 'Table'[Start Date] )
            && OR ( [Leave date] = BLANK (), [Leave date] >= MAX ( Table[Start Date] ) )
    )
)

Cumulative Summary filtered on start date and end date

Total Number Of Staff Over Time - Power BI Insights

vyiruanmsft_0-1697183556807.png

Best Regards

Community Support Team _ Rena
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

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

Hi  @ma142 ,

You can refer the following links to get it:

Get the number of starters and leavers:

Starters and leavers from monthly extract

New Starters =
VAR lastmonth =
    CALCULATETABLE (
        VALUES ( Table1[Employee ID] ),
        FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 )
    )
RETURN
    IF (
        MAX ( Table1[MonthNo] ) = 1,
        CALCULATE ( COUNT ( Table1[Employee ID] ) ),
        CALCULATE (
            COUNT ( Table1[Employee ID] ),
            FILTER ( Table1, NOT ( Table1[Employee ID] ) IN lastmonth )
        )
    )
Leavers =
VAR lastmonth =
    CALCULATETABLE (
        VALUES ( Table1[Employee ID] ),
        FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 )
    )
RETURN
    IF (
        MAX ( Table1[MonthNo] ) = 1,
        0,
        CALCULATE (
            COUNT ( Table1[Employee ID] ),
            FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 )
        )
            - CALCULATE (
                COUNT ( Table1[Employee ID] ),
                FILTER ( Table1, Table1[Employee ID] IN lastmonth )
            )
    )

How to calculate Starters and Leavers |DatesinPeriod |CountRows|

 

Get the running total of starters and leavers:

Starters/leavers - cumulative

Measure =
CALCULATE (
    SUM ( 'Table'[Hours] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Start Date] <= MAX ( 'Table'[Start Date] )
            && OR ( [Leave date] = BLANK (), [Leave date] >= MAX ( Table[Start Date] ) )
    )
)

Cumulative Summary filtered on start date and end date

Total Number Of Staff Over Time - Power BI Insights

vyiruanmsft_0-1697183556807.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
parry2k
Super User
Super User

@ma142 there are tons of blog posts/videos this? Have you tried searching on google?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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