I’ve twisted my brain into a pretzel thinking about this, so I hope this makes sense.
My team manages a classroom training course for a group of employees who are within their first 30 days with the company. For each reporting period, I need to identify 3 things:
- The numer of employees eligible to take the training (eligibility is based on job type, location, and tenure in days [within 30 days from hire date])
- The number of eligible employees who took the training
- The % of eligible employees who took the training
I have 5 tables in my data model with active and inactive relationships. I’ve include a picture of the data model below.
- ClassroomTraining - fact table w/ training attendance info (active relationship with each lookup table)
- Customer- lookup table with employee records (inactive relationships with the other data tables for eligible customer calculations)
- Region - lookup table with locations grouped into regions
- Calendar - lookup table with a standard calendar format
- CustomerGroup - lookup table with job details like level, job title, and category
(I’m including both in 1 post because I think the key to solving both is the same. Happy to split them up if that would be best.)
- I can write measures that identify eligible employees based on job type and location, but can’t seem to factor in the 30-day window from their Last Hire Date.
- Because an eligible employee’s 30-day window can include 2 report months, I need to exclude employees who took the training in the previous report month from the count of eligible employees in the next report month. For example, if an eligible employee was hired on April 25, 2018, and they took the training on April 30, I need to exclude them from the total # of eligible employees for May even though their 30-day new hire window extends into May.