How to get the new records and dropped records?

[ma142]

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

[v-yiruan-msft · Accepted solution]

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

Best Regards

[parry2k]

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