Employee count per date

[dzaba]

Hi Guys, 

I have a problem with one of my formula:

#Employee Count =

VAR selectedDate = MAX('Date'[Date])

RETURN

SUMX('VW_DMMD_DLR_INFO',

VAR employeeStartDate = [EMP_START_DT]

VAR employeeEndDate = [USER_END_DT]

RETURN IF(employeeStartDate<=selectedDate && OR(employeeEndDate>=selectedDate, employeeEndDate=BLANK() ),1,0)

)

My table looks like below. What I am expecting after this DAX, that my count will be 19, so will count all blanks and future emp_end_DT. but somehow it is counting also Andreas emp_end_dt 02-09-2021. 

FRST_NM	EMP_START_DT	EMP_END_DT
Franz-Josef	06-09-2007	01-03-2021
Tom	02-01-2012	26-03-2021
Andreas	14-11-2007	02-09-2021
Nico	09-01-2008
Michael	06-09-2007
Clemens	04-07-2007
Frank	05-09-2007
Carsten	05-07-2007
Reinhard	05-09-2007
Markus	01-02-2011
Guido	01-03-2012
Johannes	01-02-2013
Jan Henrik	08-01-2010
Rene	18-03-2013
Johannes	04-08-2014
Heino	14-08-2008
Magnus	09-01-2009
Claus	30-07-2008
Sebastian	01-01-2010
Roy	14-05-2012
Markus	14-05-2008
Norbert	10-01-2008

[amitchandak]

@dzaba , Refer my blog on the same topic , if that can help

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

[dzaba]

Hi, thanks for the link. I belive it will work for me, but I am looking for solution for this specific measure.

[v-angzheng-msft]

Hi, @dzaba 

Do you mean that the current count is 20, but the correct count is 19?
What does your date table look like? Is the count based on today's date? How do you show your measure, with a table or with a card or?
I tried to show your measure on card and got the correct result 19. I think your measure is completely correct, maybe there are errors in the details.

Could you please consdier sharing more details about it or a simple sample file without any sesentive information for further discussion?

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