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 nearly there with the calcs, on a sample dataset, just need three more calcs as stated in the excel Orange. 

 

Hoping someone can help. 

 

Link to WIP files attached.

 

Excel

 

PBIX

 

Thanks. 

 

 

 

Need Help

4 Replies

  • 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