Divide With a Sum and Filter on average column

[Addora]

I ask to compute attrition rate from two different data sources. I have 2 different sources:

1st: employee headcount (one row per employee) which has all employee details such as Name, ID, Date of Joining, Supervisor name, Department etc.

2nd: showing data for employees who resigned

I need to compute the attrition rate for selected Group and selected months. Groups and months will be selected from slicers.

the attrition function is: Sum of Leavers / Average monthly Headcount

this function must consider the slicer filter ex: if I select one group with one month the function will be


Sum of Leavers for this group and this month / Average monthly Headcount for this group and this month


and used measure, unfortunately, give non-valid results

Attrition.pbix file

gratefully,

 

[Matt Allington]

I'm on an iPad so I can't easily see the sample (but thanks for providing it). In general, this is what you would do

create a calendar table and join it to the attrition table on the depart date.
Write a measure that is simply countrows(attrition). This will give you those leaving for any slicer date or dept.

Current staff level is harder. You need to write a manual filter (no relationship)

something like this

calculate (countrows(staff),filter(staff,staff[start date] >= min(calendar(date)) &&
staff[end date] <= max(calendar(date))))