cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jgalus
Resolver I
Resolver I

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

I'm trying to recreate the below chart from the Human Resources Sample report that Microsoft offers.2017-10-24_10-16-45.png

 

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.

2017-10-24_10-21-35.png

I'm using the below formulas:

Employee Count = DISTINCTCOUNT( [Employee ID] )

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

1 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.

 

View solution in original post

2 REPLIES 2
v-yulgu-msft
Microsoft
Microsoft

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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.

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.