Calculate Employee Retention

[Brianoreilly]

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. 

[v-frfei-msft]

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]

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]

Hi @Brianoreilly,

Could you please share the file to me again?

Regards,

Frank

[v-frfei-msft]

Hi @Brianoreilly,

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

Regards,

Frank