Filter employees on different date columns

[Anonymous]

Hi community! 

I have a problem that I'm hoping someone could help me with. 

i will try to give a short explanation for context. 

I am transforming the firm's HR BI report from a method where all data prep and modelling is done in Excel first and then loaded in the report, to a fully automated report where data only needs to be refreshed in power BI. 

The data consists of employee's and the columns are information. every month new data comes in on overall headcount, but also info on new hires, retired and leaving employees. the data that come in every month is categorized by a date. I compile everything in 1 dataset that categorizes employees wether they are: Opening or closing count, a new joiner, a leaver, or a retiree. 

for the headcount, the normal date column is correct. However for the other employee categories i need the data to be filtered by other date columns;

- new joiners need to be filtered on "work relationship start date",

-leavers need to be filtered on "Termination date", 

- retires need to be filtered on "retirement date"

I think I either need to put something in the query that filters the people out if the month in the date column doesn't match the month in the other date column. 

or

A DAX formula that applies on the date slicer, so when I select a month in the report view, that for each type of employee the respective date column is chosen. 

I hope i explained the problem well enough, and that any of you can make sense of it. I am awaiting your inputs!

Kind regards

Robin

[Anonymous · Accepted solution]

Hi community,

I found a solution for the issue, I will share it here in case anyone clicks on this post with the same question.

1. in the model view (ERD), create  relationships between the date table, and all the needed date columns. make sure these relationships are inactive. Only the relationship of the original date column in main data will stay active. it will end up looking something like this; 

 

2. for calculating the headcount at any given date, it is simply the count of employees, I dont need to refer to any date column. But for Joiners, Leavers, and Retires I use the function USERELATIONSHIP. for example:

Joiners = CALCULATE ( DISTINCTCOUNT ( 'Main data'[Person Number] ); 'Main data'[Status] = "Joiners"; USERELATIONSHIP('Main data'[Work Relationship Start Date];Datumtabel[Date]) )

Retires = CALCULATE ( DISTINCTCOUNT ( 'Main data'[Person Number] )* -1; 'Main data'[Status] = "Retires"; USERELATIONSHIP('Main data'[Retirement date];Datumtabel[Date]))

3. instead of using the [Status] column as the Y-axis, I put the 3 new made measures in the visual. the final result;

as of today, only January has finalised. But we can already look ahead and see in what months people are set to join or leave the company. 

hopefully this is helpful to someone 🙂

[amitchandak]

@Anonymous , Try if this approach of HR can help in your case

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.