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