Hi everyone,
I need some guidance on a formula as I am relatively new to BI.
The below is what I have used to calculated my Annual Turnover Rate:
Employee Growth =
VAR MaxDate = MAX('Calendar'[Date])
VAR EMPCOUNT =
CALCULATE(COUNTROWS(CALCULATETABLE(Drivers, Drivers[StartDate] <= MaxDate, ALL ('Calendar'[Date]))),
(ISBLANK(Drivers[FinishDate])|| Drivers[FinishDate] > MaxDate))
RETURN IF(ISBLANK(EMPCOUNT), 0, EMPCOUNT)
Count 12 months before =
VAR SelectDate = NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Calendar'[Date])))
RETURN
SUMX(Drivers,
VAR DriverStartDate = [StartDate]
VAR DriverFinishDate = [FinishDate]
RETURN IF (DriverStartDate < SelectDate && OR(DriverFinishDate >= SelectDate, DriverFinishDate = BLANK ()),1,0))
Left past 12 months =
VAR selectedDate = LASTDATE('Calendar'[Date])
VAR selectedDate12MonthsBefore = NEXTDAY(SAMEPERIODLASTYEAR(selectedDate))
RETURN
SUMX(Drivers,
IF([FinishDate]>=selectedDate12MonthsBefore && [FinishDate]<=selectedDate, 1, 0))
Turnover % =
VAR averageEmployeesRolling12months = ([Employee Growth]+[Count 12 months before])/2
RETURN
[Left past 12 months]/ averageEmployeesRolling12months
I was wondering what will be the changes I need to make if I wanted to have the calculations not based on a rolling 12 months but tight to the Financial Year.
Could you please help?
Thanks in advance.