Employee Count Per Month DAX Not Working

Question

Hello,&nbsp;I am trying to compare the number of absences against total staff count month on month for a 12 month period.&nbsp;I was using this formula to work out the staff count per month:Staff Count Per Month = VAR selectedDate = MAX('Date'[Date])RETURNSUMX('IT Area Count Data',VAR EmployeeStartDate = 'IT Area Count Data'[Employment Start]RETURN IF(EmployeeStartDate &lt;= selectedDate, 1, 0))&nbsp;I have used this formula in other reports and it works perfectly but in this report it seems to be working oddly:I have had a look at my data model but nothing seems out of sorts can anyone tell me why this is happening. The data used in this formula contains the Employee ID number, their IT Area and their Employment Start Date. The instance of absence measure comes from a seperate absence table.&nbsp;&nbsp;Thanks!

amitchandak · Answer

Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
&nbsp;
refer if these two can help
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785
&nbsp;
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

anonymous · Answer

Thanks for replying so quickly.&nbsp;Here is some example employee data:Here is some example Absence Data:Here is what I would like a table output of the measure to be when compared to Month Year:Here is an example of a graph output with all information removed. Please imagine month year along the x axis:&nbsp;Thanks

v-yadongf-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
I tested your DAX formula and it works fine.

This my test table:

&nbsp;
Please check the following things:
1) Whether there is missing data in the Employee table.
2) Is the model relationship between the two tables normal?
&nbsp;
Best regards,
Yadong Fang
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

v-yadongf-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Has your problem been solved? If solved, please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;
Best regards,
Yadong Fang

Forum Discussion

Employee Count Per Month DAX Not Working

4 Replies

Related content

Counting Active employees per month

Monthly Count not working

Counting nulls

Missing employee

Count number of employees