Calculate the turnover of my company

[AntoineOA]

Hello guys,

I am trying to calculate my company's turnover. I already tried to fix my problem by looking for the solution on other posts but I can't find it.

I have a date table added directly on powerbi that has no direct relation in my database.

I have an employee table with a start date and an end date within the company. Some of my employees have several rows because some of them have done an internship before being employed permanently. So I added a column where I add "leaver" if the person has left the company permanently.

I have so far done this measure:
Leavers = CALCULATE(
COUNTROWS(EMPLOYEE),
FILTER( VALUES( EMPLOYEE[ValidTo]), EMPLOYEE[ValidTo] <=MAX('Date'[Date])), EMPLOYEE[Internal_move] = "Leaver")

The result of this measure when I combine it with my date table gives me a cumulative increasing number of employees per year but not the exact number of people who left during the year. So the final result for 2022 is 72 but only 6 employees left the company in 2022.

How can I fix that to get 6 for 2022 ?
Thanks in advance

[v-jayw-msft]

Hi @AntoineOA ,

What is the purpose of using "EMPLOYEE[ValidTo] <=MAX('Date'[Date])" in a formula?

Best Regards,

Jay

[Signore_Ands]

Hi @AntoineOA 

Here is my measure for leavers in the Month:

Leavers This Month =

VAR CurrentMonth = SELECTEDVALUE('Calendar_Months'[month_of_yr])

VAR CurrentYear = SELECTEDVALUE('Calendar_Months'[calendar_yr])

RETURN

CALCULATE(MAX(SUM('PC - EE List'[#]),0),

FILTER('PC - EE List',

year('PC - EE List'[Termination Date])=CurrentYear &&

month('PC - EE List'[Termination Date])=CurrentMonth)

)

The Max is in there just to make sure we have a 0 rather than a Blank, if no one left in the reporting period.
The SELECTEDVALUE bits refer to the outputs of my slicers - year and month - on the report.

This should probably would work for the End Date in your data set, where the Leaver column in populated.

Hope that helps.

[AntoineOA]

Hi @Signore_Ands 

I'm trying something like you. I splited values from my endDate column to create two news columns. One for LeavingYear and one for LeavingMonth. 
I don't really understand this part of your measure : CALCULATE(MAX(SUM('PC - EE List'[#]),0),
What is the #? 

[Signore_Ands]

In my Employee list I add a counter column (#) that has a 1 in every row. 
It's a way of doing the COUNTROWS(EMPLOYEE), that you are doing.
So by summing  the # column and filtering on the leave year and month, I get the count of leavers for that period.

Here's a snapshot of my data in case it helps: