HR Report modeling. Multiple Hirings

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