Employee Attrition Non Standard Calculation. Need Help!

[Brianoreilly]

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. 

[v-yulgu-msft]

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]

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

[Brianoreilly]

@Greg_Deckler, @Zubair_Muhammad, @Seth_C_Bauer is there any chance, you would know of a quick fix. 

So close, I just cannot get the dang thing to work. 

Thanks, 

[parry2k]

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

[Brianoreilly]

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,