Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

rolling -employee turnover calculation

Hi All,   I have below tables and want to calculate turnover rate as below .This is going to be rolling calculation for each year   turnover rate=offboarded employee(2020)/avg(dec 2020 count+d...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , if you have date then you can use time intelligence

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))

 

divide([MTD Sales], ([last MTD Sales]+[2nd last MTD Sales])/2)

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi I tried calculating offboard count using mentioned folrmulae but its not working

for 2020,  formulae is offboard _2020/avg(headcount at2019+headcount at2020)

 

WIP_C_Previous full year =
var avgLast=[DECTable N-1]+[DECTable N-2]
var avgSec_Last=[DECTable N-2]+[DECTable N-3]
var off_curr=CALCULATE(SUM(Per[OFFBOARD_COUNT]),Per[Year]=YEAR(NOW()))
var off_last=CALCULATE(SUM(Per[OFFBOARD_COUNT]),Per[Year]=YEAR(NOW())-1)
var off_last2=CALCULATE(SUM(Per[OFFBOARD_COUNT]),Per[Year]=YEAR(NOW())-2)

return

IF(YEAR(RELATED('Date'[Date]))=YEAR(NOW()), off_curr/avgLast,

IF(YEAR(RELATED('Date'[Date]))=(YEAR(now())-1),(off_last,avgSec_Last)))