Interested to see if there is a solution to this using functions, as on something similar I resorted to scripting. Applying to your example I did the equivalent of creating a new DateLink table with all dates per EmpID for which they were hired (and not terminated), so a script like this on the end of your current model;
DROP TABLE DateLink;
combined:
NoConcatenate
Load
*
Resident Hires;
left join (combined)
Load
*
resident Terms;
for i = 1 to NoOfRows('combined')
Let EmpID=peek('EmpID', $(i)-1, 'combined');
If IsNull(peek('TermDate', $(i)-1, 'combined')) then
//Load all dates after Hire
DateLink:
Load
Date,
$(EmpID) AS EmpID
Resident CommonCalendar
Where Date>=peek('HireDate', $(i)-1, 'combined');
ELSE
//Load dates between Hire and Term
DateLink:
Load
Date,
$(EmpID) AS EmpID
Resident CommonCalendar
Where Date>=peek('HireDate', $(i)-1, 'combined') AND Date<=peek('TermDate', $(i)-1, 'combined');
end if
next
Drop table combined;
To get closer to what you want you would need to add some extra logic to your expression (either through an extra field on CommonCalendar and some set analysis, or some kind of date test) to limit it to return just the month end points, otherwise you get bars for every day.
