How to find total headcount value from 2006 year to 2013 year ?

[Anonymous]

Hello Experts ,

How to find total headcount value from 2006 year to 2013 year (please look into scrrenshot) ? please advise me how to do in DAX . I want to display on card(if i am slecting 2013 year it should be display total headcount value from 2006 to 2013 or if i am selecting 2012 year it should be display total headcount value from 2006 to 2012 )

NP :headcount value ( count of employee id). year (calender table [date])

Please help me or share me DAX script

1. first table HRMS

2. second table calender

[v-juanli-msft]

Hi @Anonymous 

vimal_parmar's suggestion is useful.

In addition, please don't create relationship between "date" table and first table HRMS.
If they need a relationship for other cases,
please create another date table using "Calendarauto" function,
leave them no relationship,
use the "date" column from this new date table to use in a slicer.

Anthor tip is that if the employee id is duplicated, but you want to count the distinct ones,you could replace "Count" with "DISTINCTCOUNT".

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

[Anonymous]

@Anonymous Please try below measure> I'm assuming 2006 is minimum value for year in your table if not then add one more condition where year(date)>2006

Measure = 
VAR _selectedYear = SELECTEDVALUE('Date'[Date].[Year])
RETURN CALCULATE(COUNT(Employee[EmpId]),FILTER(ALL(Employee),YEAR(Employee[DOJ])<=_selectedYear))

[Anonymous]

Hello vimal_parmar

yeahhh DAX is correct but i want to avoid termination employee ( Date of termination NA means still employed  ).So how to avoid and filter this termination employees in this dax .Please advise me 

measure =

VAR _selectedYear = SELECTEDVALUE('Calendar'[Date].[Year])

RETURN CALCULATE(COUNT(HRMS[EmpId]),FILTER(ALL(HRMS),YEAR('HRMS'[DateofHire])<=_selectedYear)

)

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Modify below

measure = 
VAR _selectedYear = SELECTEDVALUE('Calendar'[Date].[Year])
RETURN CALCULATE(COUNT(HRMS[EmpId]),FILTER(ALL(HRMS),[Termd]=0 &&YEAR('HRMS'[DateofHire])<=_selectedYear)
)

Best Regards
Maggie

[Anonymous]

Yeahhh excellent . Finally solution is done 

Thank you so much you are really great  vimal &  v-juanli-msft