Calculate total with multiple filters

[raulawesome]

Hello, 

This is my first post. Apologies in advance for any missteps and the length

I'm trying to calculate the following per month: 

• Total employees eligible to take a specific training class within their first 30 days. 
• Total of eligible employees who took that class within their first 30 days.
• % of eligible employees who took the class per month with employees who attended in a prior month removed from the calculation (example: an employee's 30 day window includes dates in Jan and Feb. If the employee takes the class in Jan, I want to exclude them from Feb's calculations).

I have 5 tables in my data model in Excel 2016 (I have to produce a pivot table with the results):

• ClassroomTraining - fact table w/ training attendance info (active relationship with each data table)
• Customer- data table with employee records (inactive relationships with the other data tables for eligible customer calculations)
• Region - data table with locations grouped into regions
• Calendar - data table with standard calendar information
• CustomerGroup - data table with job details like level, job title, and category

Eligibility is based on an employee's new hire group value (CustomerGroup[New Hire Group] = Primary) and region (Region[EmployeeRegion] <> "Unassigned"). 

I have measures to calculate the total number of eligible employees and eligible employees attending by customer group and region, but can't seem to figure out the DAX to add the date filtering to the mix.

Grateful for any help. Thanks! 

Data model and relationships
Data ModelRelationships

[v-yuta-msft]

Hi raulawesome,

Could you please clarify more details about your measure and expected results because some of the DAX formula relay on your table relationship?

Regards,

Jimmy Tao

[raulawesome]

Thanks for the reply, Jimmy. Maybe it will help to clarify if I share the measures I'm struggling with. Apologies if this is more info than you're looking for. 

In my ClassroomTraining fact table, I have 3 measures to calculate training attendees who fall into our primary group based on their job title and location who attended the training:

1. Total primary attending = CALCULATE([Total Attendees], CustomerGroup[New Hire Group] = "Primary"). This tells me how many attendees have the job title that we target with this training.
   
   • Total Attendees = CALCULATE(DISTINCTCOUNT('ClassroomTraining'[Username]), FILTER('ClassroomTraining', 'ClassroomTraining'[Transcript Status] = "Completed" || 'ClassroomTraining'[Transcript Status] = "Pending Evaluation")). 
2. Total in-region primary attending = CALCULATE([Total primary attending], Region[Employee Region] <> "Unassigned"). This measure tells me how many of the employees in [Total primary attending] are located in a region where the training takes place. 
3. Total out-region primary attending =CALCULATE([Total primary attending], Region[Employee Region] = "Unassigned"). This measure tells me how many of the employees in [Total primary attending] are located outside of a region where the training takes place (they had to travel to take the training). 

Help needed on measures 2 and 3: Need to add an additional factor to count only the attendees who took the training within 30 days of their hire date. The Customer data table that this fact table is linked to contains a date column that identifies the last date of an employee's 30-day new hire window called End Date First 30. 

In my Customer data table, I have 3 measures to calculate the number of employees in our primary group who are eligible to take the training. 

1. Total primary customers = CALCULATE([Total Customers],USERELATIONSHIP(Customer[Job Code], CustomerGroup[Job Code]),CustomerGroup[New Hire Group]="Primary"). This measure tells me how many employees have the job title that we target with this training. 
   • Total Customers = =DISTINCTCOUNT(Customer[Employee User ID]).
2. Total in-region primary customers = CALCULATE([Total primary customers], USERELATIONSHIP(Customer[Location ID], Region[Location ID]), Region[Employee Region] <> "Unassigned"). This measure tells me how many of the employees in [Total primary customers] are located in a region where the training takes place. 
3. Total out-region primary customers = CALCULATE([Total primary customers], USERELATIONSHIP(Customer[Location ID], Region[Location ID]), Region[Employee Region] = "Unassigned"). This measure tells me how many of the employees in [Total primary customers] are located outside of a region where the training takes place (they would have to travel to take the training). 

Help needed on measures 2 and 3: Need to add factors

• To count only those employees whose End Date First 30 is greater than or equal to the 1st day of the report month and less than or equal to the last day of the report month.
• Exclude those employees who took the training in month prior to the report month. The ClassroomTraining fact table above has a [Training Start Date] column. If a customer's [Training Start Date] is less than the 1st date of the reporting month ('Calendar'[MonthShortName]), I want to exclude them from the count for the reporting month.

Thanks again!