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+dec 2019 count)

 

Data table

 

Any suggestion would be appreciated.

 

Thank you

 
 

 

 

 

Need Help

2 Replies

  • 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)))