Forum Discussion

Brianoreilly's avatar
Brianoreilly
Helper II
7 years ago

Calculate Employee Retention

Hi Folks,    Our company uses non standard retention calcs.    Work on the premise of: Cumulative(Number of leavers) divided by the Average Monthly Headcount for the selected periods.   I am ne...
Need Help
v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Brianoreilly,

 

Create the measures as below.

 

Cumulative Headcount Rolling = CALCULATE(SUM(Table1[Headcount at Period End]),FILTER(ALL(Table1),Table1[Fiscal Month No]<=MAX(Table1[Fiscal Month No])))
Cumulative Headcount Rolling ave = [Cumulative Headcount Rolling]/MAX(Table1[Fiscal Month No])
Cumulative new leavers = CALCULATE(SUM(Table1[New Leavers in Month]),FILTER(ALL(Table1),Table1[Fiscal Month No]<=MAX(Table1[Fiscal Month No])))

 

For more details, please check the pbix as attached.

 

Regards,

Frank

  • Brianoreilly's avatar
    Brianoreilly
    Helper II
    7 years ago

    Hi v-frfei-msft, 

     

    Could you look at my PBIX file attached earlier?

     

    I am using measures, so cannot do as you have stated. 

     

    Any help appreciated.

     

    Thanks, 

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi Brianoreilly,

       

      I cannot download your files by the links that you shared. Could you please share them again?

       

      Regards,

      Frank

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi Brianoreilly,

       

      Could you please share the file to me again?

       

      Regards,

      Frank