Filter employees on different date columns

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 🙂