Employees headcount

[Anonymous]

Hello All, 

I am working on power bi and I am facing a challenge in calculating total number of employees, 

I have seen several articals in which they create inactive relationship between calendar and employee table. That works.

But my problem is I want to filter the data too and I have several other tables.

Employee table - Emplyee ID, start date and end date (if present else blank)

Timesheet table - This is my transaction table and I have several other tables as well like cost centre.

So, Timesheet is my transaction table and employee table is master's table.

Please do suggest me some other methods to make it work.

Below are the DAX which is used and they worked but for inactive relationship of employee and calendar table.

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

Eployee Joined =

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

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

return

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

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

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

return

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

[Mariusz]

Hi @Anonymous 

Try expanding your dates like in the video below, also see the attached for reference.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[amitchandak]

@Anonymous ,

Please if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Actual Relations

Left Side actual result, right side by using your DAX measures

Hello @amitchandak 

Thankyou for your reply, I have attached 2 images, the first one shows the actual relations which i made and second one shows the results, 

Results in left table are the actual results and right ones i created by using the dax which you shared.

Please have a look what I atually want, and do suggest 

Thanks!

[amitchandak]

@Anonymous , can you share the formula you used. Do these dates have a timestamp. hire number seems off by far at few places.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

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.

[Anonymous]

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! 

[v-easonf-msft]

Hi, @Anonymous 

Have  you tried the funtion of "userelationship"  when you need use  an inactive relationship?

Here is a simple sample.

pbix attached

Hope it will help you.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Yes, I have tried that too, nothing worked