Hello all,
I apologize if the question has been asked somewere else but I couldn't find an answer to my issue anywhere in the PBI forum.
I have currently an issue computing in PBI a graph displaying the evolution of the YTD employee turnover per month.
We are using the following formula to compute the YTD turnover: YTD turnover = (YTD nb employee in + YTD nb employee out) / (2* nb employee first month of the year).
In my graph I would like to display:
- In January: (nb employees in for January + nb employees out for January) /(2* nb employee in January)
- In February: (nb employees in for January & February + nb employees out for January & February ) / (2* nb employee in January)
- In March: We extend the logic above to include in & out employees from January to March
- ...
Below is a subset of the date I'm using to compute this metric, knowing that 2 in the status field means that an employee is part for that month of the current workforce:
| Year | Month | Employee ID | Status | Affiliate |
| 2022 | 1 | 81 | 2 | A |
| 2022 | 1 | 85 | 2 | A |
| 2022 | 1 | 86 | 2 | A |
| 2022 | 1 | 89 | 2 | A |
| 2022 | 1 | 93 | 2 | A |
| 2022 | 1 | 105 | 2 | A |
| 2022 | 1 | 1393 | 2 | A |
| 2022 | 1 | 3484 | OUT | A |
| 2022 | 1 | 6321 | IN | A |
| 2022 | 1 | 6321 | 2 | A |
| 2022 | 2 | 81 | 2 | A |
| 2022 | 2 | 85 | 2 | B |
| 2022 | 2 | 86 | 2 | B |
| 2022 | 2 | 89 | 2 | B |
| 2022 | 2 | 93 | 2 | B |
| 2022 | 2 | 105 | 2 | B |
| 2022 | 2 | 1393 | OUT | B |
| 2022 | 2 | 6321 | OUT | B |
| 2022 | 3 | 81 | 2 | B |
| 2022 | 3 | 85 | 2 | B |
| 2022 | 3 | 86 | 2 | B |
| 2022 | 3 | 89 | 2 | B |
| 2022 | 3 | 93 | 2 | B |
| 2022 | 3 | 105 | 2 | B |
| 2022 | 3 | 3735 | IN | B |
The issue I have is I don't know how to compute the denominator of my YTD turnover (i.e. 2*Employee_firstmonth), as I want to reuse for every month in my monthly YTD turnover graph a numbre that is fixed and remains constant in time (that measure however should change if I'm filtering by affiliate). Currently I'm using the following formula that returns 0 for Feb and March when I'm ploting it per month:
Employee_firstmonth:=
CALCULATE(
DISTINCTCOUNT('HR_In_And_Out'[Employee ID]),
FILTER('HR_in_and_Out','HR_in_and_Out'[HistoryType_Cd]="2"),
FILTER(HR_In_And_Out',HR_in_and_Out'[Month]="1")
)
What is the best approach to compute it?
Also to compute the numerator of the YTD turnover I'm using the TotalYTD(DistinctCount) function on the "IN" and "OUT", is it the correct approach if I want to be able to plot my measure per month?
Many thanks for your help,
Thibault
