PowerBI and Cezanne connection

Hi, jelenagerjevic 

In order to calculate the number of employees for each month, you need to determine whether an employee is active in a particular month based on the Hire Date and Termination Date. This typically involves using the RELATEDTABLE or CALCULATE functions in conjunction with the FILTER function to filter for employees in a given month. The following example metrics (may need to be adjusted based on your data model):

Monthly Headcount =   
CALCULATE(  
    DISTINCTCOUNT(Employment[EmployeeID]),  
    FILTER(  
        ALL(Calendar),  
        Calendar[Month] = MAX(Calendar[Month])  
            && EXISTS(  
                Employment,  
                Employment[Hire Date] <= MAX(Calendar[Date])  
                    && (ISBLANK(Employment[Termination Date]) || Employment[Termination Date] >= MAX(Calendar[Date]))  
            )  
    )  
)

In order to calculate the turnover rate, you need to determine how many employees have left in a given month. This usually involves comparing the number of employees in the current month to the previous month.
Example metric (again, needs to be adjusted):

Monthly Turnover =   
VAR CurrentMonthHeadcount = [Monthly Headcount]  
VAR PreviousMonthHeadcount =   
    CALCULATE(  
        [Monthly Headcount],  
        PREVIOUSMONTH(Calendar[Date])  
    )  
RETURN  
    IF(  
        PreviousMonthHeadcount > 0,  
        DIVIDE(  
            CALCULATE(  
                DISTINCTCOUNT(Employment[EmployeeID]),  
                FILTER(  
                    ALL(Calendar),  
                    Calendar[Month] = MAX(Calendar[Month]) - 1  
                        && EXISTS(  
                            Employment,  
                            ISBLANK(Employment[Termination Date])  
                                || Employment[Termination Date] = EOMONTH(MAX(Calendar[Date]), -1)  
                        )  
                )  
            ),  
            PreviousMonthHeadcount  
        ),  
        BLANK()  
    )

You can filter the results by Contract Term and Termination Reasons fields by using slicers or filters in the visualization.

When calculating headcounts and turnover rates, be sure to consider situations where time overlaps (e.g., where an employee joins and leaves within a month). If you have multiple contract durations or deployment records associated with the same employee in your data, you may need to use more complex logic to ensure that each employee is only counted once.

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly