Two date columns connecting to Date table

Power BI Data Model
1

Hi forum friends,

I am working on an Employee Headcount calculation. There are two columns in employee table: hire date and termination date. I am not sure how to set up the relationship between employee table and Date table.

I’d like to calculate the number of leavers, number of hires, and number of current headcount.

I tried two ways: one is create two inactive relationship, and only make one relationship active to calculate the above 3 metrics. Another approach is not using USERELATINSHIP in the calculate function. Two ways got same results as showed below.

Can you kindly suggest why the results same when not specify which relationship is active?

Many thanks!!

Number of leavers:

  1. Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],‘Date’[Date]),not(ISBLANK(Employee[End Date])))

  2. Terminated Employees_test = CALCULATE(COUNT(Employee[Employee Id ]),not(ISBLANK(Employee[End Date])))

Hired Employees

  1. Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],‘Date’[Date]) )
  2. Hired Employee_test = CALCULATE(COUNT(Employee[Employee Id ]) )
    Current_employee.pbix (80.8 KB)

image

2

@chenwei5712 ,

The reason is because in your current analysis, you are not filtering on Date, therefore it doesn’t matter whether the relationship to the Date table is active or not - the result will be the same either way.

However, filter on date (say by adding a slicer) and you will immediately see the difference between the measures based on whether the relationship to the Date table is active or not:

image
image810×432 9.91 KB

I hope this helps clarify things.

Best,

  • Brian
1 Like
3

Hi @chenwei5712 , did the response provided by @BrianJ help you solve your query?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

4

Hi @chenwei5712, we’ve noticed that no response has been received from you since September 25.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @chenwei5712, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please create a new thread.