New Hire Count, New Hires SPLY and Actives YOY % Change

[jgalus]

I'm trying to recreate the below chart from the Human Resources Sample report that Microsoft offers.

However, I'm unable to create the YOY % Change and when I show the SPLY, I can only do it by actually showing the las tyear data which leaves me with the below chart.

I'm using the below formulas:

Employee Count = DISTINCTCOUNT( [Employee ID] )

Employee Count LY = CALCULATE( DISTINCTCOUNT( [Employee ID] ), SAMEPERIODLASTYEAR( [Hire Date] ) )

[v-yulgu-msft]

Hi @jgalus,

What do you mean "unable to create the YOY % change"? Since you have get the result of this year and last year employee count, you only need to create another measure:

YOY%=DIVIDE(([Employee Count]-[Employee Count LY]),[Employee Count LY])

Then, add this YOY measure into line section.

Regards,
Yuliana Gu

[jgalus · Accepted solution]

@v-yulgu-msft

Thank you for the help! That did the trick for the YOY % Change. For reference, the solution was below:

YOY % Change = 
DIVIDE(
	(DISTINCTCOUNT([Employee ID])-[Employee Count LY]),[Employee Count LY]	
)

As for getting the Employee Count SPLY to only show the one year as in the HR dashboard, I had to create a date table and tie that to the chart. Then I was able to set a relative filter to the calendar table's date field.