Employee turnover monthy

[dzaba]

Hi,

I am looking for a help with DAX to calculate the Employee turnover. 

My data look like that:

Table name: Staff Population

To make this calculation I need 3 measuers:

1. The number of active employees at the beginning of the month 

2. The number of active employees at the end of the month 

3. The number of employees who left during that month 

I think that I handled with the second one (like below) but still need a help with 1 and 3.

End month employee =

CALCULATE( COUNTROWS( 'Staff Population' ),

FILTER( VALUES( 'Staff Population'[Start Date] ), 'Staff Population'[Start Date] <= MAX( 'Dates'[Date] ) ),

FILTER( VALUES( 'Staff Population'[End Date] ), OR( 'Staff Population'[End Date] >= MIN( 'Dates'[Date] ), ISBLANK( 'Staff Population'[End Date] ) ) ) )

[v-deddai1-msft · Accepted solution]

Hi @dzaba ,

You can use the following three measures:

Start month employee = CALCULATE(COUNTROWS('Staff Population'),FILTER('Staff Population','Staff Population'[Start Date]<=MIN('Date'[Date])&&OR('Staff Population'[End Date]>=MIN('Date'[Date]),ISBLANK('Staff Population'[End Date])))) 

end month employee = CALCULATE(COUNTROWS('Staff Population'),FILTER('Staff Population','Staff Population'[Start Date]<=MAX('Date'[Date])&&OR('Staff Population'[End Date]>=MAX('Date'[Date]),ISBLANK('Staff Population'[End Date])))) 


left employee = CALCULATE(COUNTROWS('Staff Population'),FILTER('Staff Population','Staff Population'[End Date] >=MIN('Date'[Date])&&'Staff Population'[End Date]<=MAX('Date'[Date])))

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

Best Regards,

Dedmon Dai

[amitchandak]

@dzaba , refer to my HR blog. Use MIn(Date[Date] for start of month nad Max(Date[Date])  for end of month

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...