Here is the layout of my Job/Employment fact:
emp_id, bgndate_id, enddate_id, dept_id,status_id,year_val(not an ID)
Each of the ID columns above is a reference to a dimension(employee, begin date, end date, department, status).
What I want to be able to do is:
where bgndate <= 1/1/year_val<= enddate
and emp_status in ('VAL1','VAL2')
i.e count only people who have a record with begin and end dates spanning the begining of any year.
The bgdate, enddate I mention above in my pseudo-query would have to be obtained from the respective date dimensions.
The begin and end dates function as dates which determine duration for which any record is valid.
How would I go about buidling a measure/calculated measure to accomplish the above ?