Turnover Rate -Non-rolling for current year.

[apaulso9]

Hello all,

Using DAX I calculated a rolling 12 month turnover rate. However, when I use a date slicer and select the current year it calculates back twelve months. Is there a way to calculate the turnover while seperating the years out? When I click on 2023 I would like it to calculate the turn over from January 1st to now. Below are my dax equations for my rolling turnover measure. Any help would be appreciated. Thank you very much.

Employee Turnover rate =

VAR averageEmployeesRolling12months = ([Employee Count]+[Employee Count 12 months before])/2

RETURN

[Employees who left past 12 months]/ averageEmployeesRolling12months

Employee Count =

VAR selectedDate = MAX('Date'[Date])

RETURN

SUMX('All Employees',

VAR employeeStartDate = [Date Started]

VAR employeeEndDate = [Date Terminated]

RETURN IF(employeeStartDate<= selectedDate && OR(employeeEndDate>=selectedDate, employeeEndDate=BLANK()),1,0)

)

Employee Count 12 months Before =

VAR selectedDate = NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Date'[Date])))

RETURN

 SUMX('All Employees',

 VAR employeeStartDate = [Date Started]

 VAR employeeEndDate = [Date Terminated]

 RETURN IF(employeeStartDate<=selectedDate && OR(employeeEndDate>=selectedDate, employeeEndDate=BLANK() ),1,0)

 )  

[v-shex-msft · Accepted solution]

Hi @apaulso9,

If you want to use slicer to achieve selector effect instead of filter effects, I'd like to suggest you create an unconnected table a source of slicer and you can use Dax expression to extract the selection to compare and use in the current expression to filter with table records.

Regards,

Xiaoxin Sheng