YTD New Starts

[yaman123]

Hi all, 

I am looking for some help on how i can calculate the YTD number of new starts with the company. I have a measure which calculates the monthly number per month and i use this in a pie chart but i need the same for YTD from the chosen month from a slicer.  I am looking to also have the same for leavers, so chosen months leavers and YTD leavers. 

I have the below for monthly new starts: 

Monthly New Starts =

var currentdate = MAX('Date'[Date])

RETURN

CALCULATE(DISTINCTCOUNT('Table1'[EMP_NO]),

'Table1'[DATE_OF_EMPLOYMENT] >= DATE(YEAR(currentdate),MONTH(currentdate),1)

&& 'Table1'[DATE_OF_EMPLOYMENT] <= DATE(YEAR(currentdate),MONTH(currentdate)+1,1)-1)

[v-shex-msft]

HI @yaman123,

Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[littlemojopuppy · Accepted solution]

Hi @yaman123 

Try these measures instead...

New Starts = 
	CALCULATE(
		DISTINCTCOUNT('Table1'[EMP_NO]),
		USERELATIONSHIP(DateTable[Date], 'Table1'[DATE_OF_EMPLOYMENT])
	)

YTD New Starts =
	TOTALYTD(
		[New Starts],
		DateTable[Date]
	)

Your measure for New Starts is unnecessarily complicated.  If you use the measure above in a matrix or chart in the axis, filter context will automatically limit the data included for that year/month only.

You should create two relationships between your date table and Table1...one on date of employment and the other on date of termination (whatever you named the field).  You can use these same measures to calculate leavers as well...all you'd have to change is in the USERELATIONSHIP change date of employment to date of termination.

Hope this helps! 

[amitchandak]

@yaman123 , you can use time intelligence with date table for YTD. If you have two dates then you can join both with date table. there will be one inactive join which you can use using userelation

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Two dates

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://youtu.be/e6Y-l_JtCq4

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.