Syncing Two Date Columns Through Visual

[Anonymous]

Hello! 

I am trying to connect two different date columns from one table to a neutral Date table, but unfortunately, I cannot have two active relationships. This article was incredibly useful in doing the calculations, but when I click on the bar in my visual, my other visuals only drill through to one of the date columns (open date-the one with an actual active relationship with the Date table). I'm hoping someone here can help me find a way to sync up my open and close dates. When you see the screenshots and attachment, you can see when you click on the bar, you will only return the employees hired that month (using the example provided in the article). I would like to see both employees who have joined and left the company in each month, affecting other visuals in the dashboard as well.

https://1drv.ms/u/s!AoiqWZf7fpqIbFhyAs-lT25AreM?e=M5h4za

[amitchandak]

@Anonymous , not very clear.

You have :

Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Date'[Date]),not(ISBLANK(Employee[End Date])))

Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Date'[Date]) )

if required you can add these two.

Waterfall you are plotting current employee, which taking care of both Hire and Termination

[Anonymous]

Hi @amitchandak to clarify, yes I used the Hiring and Terminating columns. Subtracted the two calculations to receive what I want on my visual (can get a better understanding in my onedrive link). If you click on the waterfall visual for a specific month, you will only see employees who had been hired that month in my table visual. For example, 2019 in the waterfall shows we had lost one employee overall that month, but when you click on that year, you see the two employees who had joined that month in my table visual (meaning three left that year). How can I see both the employees who joined and left the company in 2019 in my table? I can't connect both to the Date table because I can only have one relationship. You mentioned creating a duplicate Date table earlier? But I can't picture how that would work.

[amitchandak · Accepted solution]

@Anonymous , I have taken that file. It seems like the file, I shared on the blog with some modification.

Anyways. I added hired count and termination count to the table now when you will drill, both measures will force the data to be visible.

Please find the attached file after signature.

[Anonymous]

Thank you so much, @amitchandak!!! This solution seems to work!