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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jelenagerjevic
Regular Visitor

PowerBI and Cezanne connection

Dear everyone,

I got stuck with my people analytics. I have connected our HR system Cezanne with OData and one of the goals is to calculate headcount and turnover. The thing is, I have 3 different tables with the data I need (Employment, EmploymentContracts and Deployment). 

In the Employment table, I have data like Termination Date, Termination Reasons, and Hire Date. 
In the EmploymentContracts table, I have the data on Contract Terms and effective dates. 
In the Deployment table there is data about Departments, effective dates, position titles, etc. Some employees had 2 or more contract terms in one Deployment or vice versa (the dates are not the same). 
I would like to calculate headcount and turnover per month and year and need to filter them by contract terms and termination reasons. Can someone help me with the data model and/or measures I need to create? I would appreciate so much! 
I have already created a calendar table. 

2 REPLIES 2
hackcrr
Solution Sage
Solution Sage

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

 

Hi, thank you for explaining the calculations!
I have one more question; as I have three tables with different dates (and each containing effective from/effective to, for some employees even multiple rows) would I be able to do something if I create nonactive relationships and use USERELATIONSHIP function?
Thank you once again!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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