Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @ashokr12,
Do you have a calendar table linked to your employee table?
Let us know in order to help.
Thx.
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!
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
Hi @Interkoubess!
I referred to this article, which recommends not to link the date table and employee table,
let me know what you think, Thanks!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |