Forum Discussion

Brianoreilly's avatar
Brianoreilly
Helper II
7 years ago

Employee Attrition Non Standard Calculation. Need Help!

Hi Folks, 

 

I have a problem calculating Employee Attrition and retention in my Org. 

Reasoning being, that it is a non standard way. 

 

I currently have a WB that is calculating the standard way.

I need a number of measures to try and calculate it via our methodology. 

1: Count of Months between To & From Slicer. 

2: Cumulative of Month End Headcount Dependent on To & From Slicer. 

3: Cumulative of Month End Leavers Dependent on To & From Slicer. 

4: Average ME Headcount : Measure 1 divided by Measure 2. 

5: Total Attrition %: Measure 3 Divided by 4. 

 

Find PBIX of Current and Excel of Idea scenario attached.

One Drive

 

 

Appreciate any help. 

 

 

 

Thanks!

Brian. 

Need Help

5 Replies

    • parry2k's avatar
      parry2k
      Super User
      7 years ago

      Brianoreilly I just downloaded your report, seems like you have done lot of work, which items are still outstanding and/or need assistance?

      • Brianoreilly's avatar
        Brianoreilly
        Helper II
        7 years ago

        Hi, 

         

        And thanks for your time!

         

        Here is what I need. 

         

        I need a number of measures to try and calculate it via our methodology. 

        Measure.

        1: Count of Months between To & From Slicer. 

        2: Cumulative of Month End Headcount Dependent on To & From Slicer. 

        3: Cumulative of Month End Leavers Dependent on To & From Slicer. 

        4: Average ME Headcount : Measure 1 divided by Measure 2. 

        5: Total Attrition %: Measure 3 Divided by 4. 

         

        The main thing I can't get working is the cumulative figures. 

        I can get the cumulative to work from the begging of the dataset to the limit on the slicer (max date on slicer.)

        For instance I can make a measure that cumulative sums from start of data set to 30 June 2016 for instance.  

        But cannot get it working for a minimum date. For instance 15 March 2015 to 31 July 2016. 

         

        Hope this makes sense.

         

        Thanks, 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Brianoreilly,

     

    After testing with your sample file, I have worked outsome of your requirements.

     

    But you need an extra calendar table which is unrelated to the Employee Table. Then, add date field from the second calendar table into slicer.

    count (month) =
DATEDIFF ( MIN ( 'Calendar2'[Date] ), MIN ( 'Calendar'[Date] ), MONTH ) + 1

Cumulative Leavers End Headcount =
SUMX (
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Year Month] <= MIN ( 'Calendar'[Year Month] )
    ),
    [Total leavers]
)

    Best regards,

    Yuliana Gu

    • Brianoreilly's avatar
      Brianoreilly
      Helper II
      7 years ago

      Hi Yuliana, 

       

      I can't seem to get this to work. 

       

      Could you send me on your solution file. 

       

       

      Would be really appreciated. 

       

      Thanks. 

      Brian