Running total of leavers

[Anonymous]

Hi all

I have an HR Report with the following data

And I like to create measures that count:

• Number of starters in each month/year
• Number of leavers in each month/year
• Cumulative (or running total) of starters in year by month
• Cumulative (or running total) of leavers in year by month

I've tried using the Quick Measure options but cant quite get it to work correctly - can anyone help please?

Thanks all!! 🙂

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

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

[PC2790]

Hey @Anonymous ,

You can try:

NoofStarters = 
CALCULATE(COUNT(EmployeeTable[id]), USERELATIONSHIP(EmployeeTable[startdate],'Calendar'[Date]))

NoofStarters_cummulative = CALCULATE(EmployeeTable[NoofStarters],FILTER(ALLSELECTED('Calendar'[Date]),Calendar[Date]<=Max(Calendar[Date])))

NoofLeavers = CALCULATE(COUNT(EmployeeTable[id]),FILTER(EmployeeTable,EmployeeTable[enddate] <> BLANK() && EmployeeTable[enddate] > TODAY()))

NoofLeavers_cummulative = CALCULATE(EmployeeTable[NoofLeavers],FILTER(ALLSELECTED('Calendar'[Date]),Calendar[Date]<=Max(Calendar[Date])))

You can replace the column name and table name as per your dataset.

Attaching the sample file for your reference.