Employees headcount

Hello amitchandak 

What I did is as I showed my model looks like that, and I used the formula which you shared for inactive relationships and the result comes out to be the one on right hand side.

Employee Table has ID, Start date and End date

Calendar table has Date, year, month, period

And in my model I have joined calendar table with timesheet table on date basis and 

Joined timesheet table with employee table on ID basis

when I make 2 inactive relation with calendar and employee table. Then results comes out to be as I attached earlier.

 Basically timesheet is my transaction table and employee, Workorder, costcentre tables are my fact table.

All tables are directly joined with timesheet table.

Sorry, I can not share the data as its very sensitive. Can you prepare a model and work on it by taking random values.

Thanks! 

Hello, I have basically 3 tables, Calendar Table in which i have dates from 1/1/2019 to 12/31/2020 Timehseet table in which I have timesheet data, suppose I have 1000 employees and in a particular week only 800 employees have filled time sheet so it will have data for just 800 emplyee for latest week.
and last i have employee table in which i have all employee information, date of joining and date of termination 

i made an active relation with calender's date to timesheet date
and another active relation of timesheet employee id with employee's table employee id

and 2 inactive relation of emplyee and calendar table on start date and end date with calendar date

and i am using these measures 

Headcount = CALCULATE(COUNTROWS('Employee count'),FILTER(VALUES('Employee count'[End_Date]),OR(ISBLANK('Employee count'[End_Date]),'Employee count'[End_Date]>=MIN('Calendar'[Date]))),FILTER(VALUES('Employee count'),[Start_date]<=MAX('Calendar'[Date])))

 

 

 

Employee Left = var a = YEAR(MIN('Calendar'[Date]))

var b = MONTH(MIN('Calendar'[Date]))

return

CALCULATE(COUNTROWS('Employee count'),FILTER(VALUES('Employee count'[End_Date]),YEAR('Employee count'[End_Date])=a && MONTH('Employee count'[End_Date])=b))

 

 

 

Eployee Joined =

var a = YEAR(MAX('Calendar'[Date]))

var b = MONTH(MAX('Calendar'[Date]))

return

CALCULATE(COUNTROWS('Employee count'),FILTER(VALUES('Employee count'[Start_date]),YEAR('Employee count'[Start_date])=a && MONTH('Employee count'[Start_date])=b))

 

 

the result of these dax measure is not coming okay because these measures are following the active path to filter data which is calendar-timesheet-employee, but i want the calendar to communicate directly with employee.  

 

Can you please help me out I am stuck in this.

 

 

This is the actual result. 

 

Please help me out.

Thanks in advance.