HR Report modeling. Multiple Hirings

[Jensej]

Hello Everyone

I have a problem working with HR Dates in Power BI. Ive seen a lot of tutorials on how to make HR Dashboards and so on but none that solves my issue. 

In every tutorial one employee have 1 ID, 1 Hire Date and 1 Termination Date (or null). 

My problem is that i have returning Employees (rehire) and therefor i have a separate history table with (EmpID,Hire Date,Termination Date).

Can someone explain or rebuild my pbix model so that i can use like they do in all tutorials. 

My goal is to be able to do all standard HR calculations/visual. 

For example a Stacked Chart over time with Current staff and Turnovers where i can filter on Departments.

Here is my pbix file:

https://rossfeld-my.sharepoint.com/:u:/g/personal/jens_frejd_rossfeld_ch/EYylGbWV1CxMgLIMF3AzMKsBCb_... 

[AlB]

@Jensej 

Well, with a relationship the engine would be filtering for exact matches,  if for instance you had a relationship between DateT[Date] and Table1[Hire date], when placing YearMonth the visual you would be filtering the rows that have Table1[Hire date] within that month. But that is not what we need here, so it is not useful

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

Hi @Jensej 

Have a look at the attached file for a possible solution (or a start to one). It includes a new relationship, a slight modification to how YearMonth is calculated and 3 measures. You might have to tweak them a bit to get them to count the staff exactly where you want:

Number of currently active staff = 
VAR minDate_ = MIN ( 'Date'[Date] )
VAR maxDate_ = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( EmpHistory[EmpID] ),
        EmpHistory[Hire Date] <= maxDate_,
        EmpHistory[Termination Date] >= minDate_ || ISBLANK ( EmpHistory[Termination Date] )
    )

Number of staff leaving = 
VAR minDate_ = MIN ( 'Date'[Date] )
VAR maxDate_ = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( EmpHistory[EmpID] ),
        EmpHistory[Termination Date] >= minDate_,
        EmpHistory[Termination Date] <= maxDate_
    )

Cumulative number of staff leaving = 
VAR currentYM_ = SELECTEDVALUE ( 'Date'[YearMonth] )
RETURN
    SUMX (
        CALCULATETABLE (
            DISTINCT ( 'Date'[YearMonth] ),
            'Date'[YearMonth] <= currentYM_
        ),
        [Number of staff leaving]
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Jensej]

@AIB Thanks a lot for taking your time

It works really good 😊. What i don't understand is how it can work without any connection to the Date table? I was sure i need to make some connections from Hire/Termination Date to the Date Table. Can you please explain why that's not needed?