YTD Average Head count using Averagex and DatesYTD

[ashokr12]

I  have master list of employees with joining date and relieving date, I have managed to calculate the Head count on any given day

with Measure [HC]=[Op]+[Joiners]-[Attrition]  (Op: Opening Head count on that day), where [Op] [Joiners] and [Attrition] are measures.

Once I know the daily head count, I am trying to calculate Average Head count on YTD basis using the  formula

AvgHC=Averagex(Datesytd('Date'(DateKey),[HC])

I am checking the result by month and measure is giving correct result for January but for other months it is ignoring the previous months data and calculating Average Head count for only the dates in that month.

Tried many types of filters still could not understand why it is happening. been scratching my head since two days.

Please help!!

Thanks in Advance

[Interkoubess]

Hi @ashokr12,

Do you have a calendar table linked to your employee table?

Let us know in order to help.

Thx.

[ashokr12]

Hi @Interkoubess!

I have a calendar table, but did not link it to the employee table (I read somewhere that that way the table works on any date selected, so did not link it)

Thanks!

[Interkoubess]

Hi @ashokr12,

You have to link them in order to use time intelligence functions.

I am not sure it will work if not.

Let us know if you connect them...

Thx. Ninter

[ashokr12]

Hi @Interkoubess!

I referred to this article, which recommends not to link the date table and employee table,

https://powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-campbe...

let me know what you think, Thanks!

[inetguru · Accepted solution]

Hello. I write about it in this post http://community.powerbi.com/t5/Desktop/Calculate-Employees-Turnover/m-p/189183#M83239. Try it.

[ashokr12]

Hi @inetguru

Followed your soultion and implemented it in my DAX. It is working!

Thanks a lot!!

Thanks @Interkoubess for taking interest in the problem!