Good Day All,

I am working through an HR report of current staff and turnover. I’m not able to use the calculations from the Videos of current staff calculation, and the further formulas for turnover, as my data model is very different than what is represented.

I’m having difficulty working out the turnover calculation as required by my HR management.

They desire turnover to be calculated as:

Monthly terminations / Average EOM Staff

My PBIX file is here: HRKPI.pbix (945.1 KB)

My data Model:

Hires Calculation

Hires =

CALCULATE([Total Actions],

FILTER(VALUES(HR_Actions[ACTION]),HR_Actions[ACTION] = “HIRE”)

)

ReHires Calculation

ReHires =

CALCULATE([Total Actions],

FILTER(VALUES(HR_Actions[ACTION]),HR_Actions[ACTION] = “REHIRE”)

)

Terminations calculation

Terms =

CALCULATE([Total Actions],

FILTER(VALUES(HR_Actions[ACTION]),HR_Actions[ACTION] = “TERMINATION”)

)

I then compute Running Totals of these three measures using this pattern:

RT_Hires =

CALCULATE([Hires],

FILTER(ALL(Dates[Date]), Dates[Date] <= MAX(Dates[Date])

)

)

And finally calculate current staff as such:

Current Staff = ([RT_Hires] + [RT_REHires]) - [RT_Terms]

These formulas are working correctly. What I cannot seem to calculate is the monthly moving average, i.e. I need to add the EOM staff count for each month, then divide by the distinct count of preceding Months.

I’m simply trying to aggregate the monthly totals for now, and have not been able do so at the month level. I’ve been able to obtain an accurate result for the sum of overall EOM staff by using this formula:

Cumulative monthly Staff =

Var MinDate = Calculate(MIN(Dates[Date]),ALLSELECTED(Dates))

Var MaxDate = CALCULATE(MAX(Dates[Date]),ALLSELECTED(Dates))

Var DateRange = FILTER(ALL(Dates), Dates[Date] >= MinDate && Dates[Date] <= MaxDate)

Return

Sumx(Filter(

SUMMARIZE(DateRange, Dates[Short Month],

“Staff”,[Current Staff],

“MonthNumber”, MIN(Dates[Short Month])),Dates[Short Month] <= MAX(Dates[Short Month])),

[Current Staff])

I need a nudge in the right direction to complete this calculation. What I am after is here:

Any assistance will be much appreciated.

Regards,

Jamie