title: DAX - Calculating at the Line Level (SUMX) with Multiple Filters
body: <p>What I'm trying to get DAX to do is:</p>
  
  <ol>
  <li>Look across each row in a table of HR data.</li>
  <li>Identify the start date of the employee ("employee a")</li>
  <li>Sum up the number of other employees in the table with the following filters applied:<br>
  a. Successfully completed their assignment<br>
  b. Ended their assignment BEFORE the start date + 31<br>
  c. Ended their assignment AFTER the start date - 31 (which is to say within a month of employee a's start date)<br>
  d. Started before employee a (to not count employee a or anyone in their cohort in the count)<br>
  e. Has the same job title as employee a.  </li>
  </ol>
  
  <p>This is essentially asking the question in normal English "for each of my employees, how many other employees with the same job title ended their assignments successfully within a month of that employee starting?" and in DAX is basically just "how do I apply multiple filter criteria to a SUMX or COUNTAX measure/calculated column?"  </p>
  
  <p>The measure I've already tried is:</p>
  
  <pre><code>Contractors Available = COUNTAX(
  'BAT VwRptMspAssignment',
      CALCULATE(
          DISTINCTCOUNT('BAT VwRptMspAssignment'[assignmentgk]),
          FILTER(
              FILTER(
                  FILTER(
                      FILTER(
                          FILTER(ALL('BAT VwRptMspAssignment'),
                          'BAT VwRptMspAssignment'[End.Date]&lt;EARLIER('BAT VwRptMspAssignment'[Start.Date])+31),
                      'BAT VwRptMspAssignment'[End.Date]&gt;EARLIER('BAT VwRptMspAssignment'[Start.Date])-31),
                  'BAT VwRptMspAssignment'[Start.Date]&lt;EARLIER('BAT VwRptMspAssignment'[Start.Date])),
              'BAT VwRptMspAssignment'[EoaReason]="Successful Completion"),
          'BAT VwRptMspAssignment'[JobPostingTitle.1]=EARLIER('BAT VwRptMspAssignment'[JobPostingTitle.1]))
          )
      )
  </code></pre>
  
  <p>And the calculated column I tried was:</p>
  
  <pre><code>Contractors Available.1 = SUMX(
  FILTER(
          FILTER(
              FILTER(
                  FILTER(
                      FILTER(
                          FILTER(ALL('BAT VwRptMspAssignment'),
                              'BAT VwRptMspAssignment'[customergk]=EARLIER('BAT VwRptMspAssignment'[customergk])),
                          'BAT VwRptMspAssignment'[JobPostingTitle.1]=EARLIER('BAT VwRptMspAssignment'[JobPostingTitle.1])),
                          'BAT VwRptMspAssignment'[End.Date]&lt;EARLIER('BAT VwRptMspAssignment'[Start.Date])+31),
                      'BAT VwRptMspAssignment'[End.Date]&gt;EARLIER('BAT VwRptMspAssignment'[Start.Date])-31),
                  'BAT VwRptMspAssignment'[Start.Date]&lt;EARLIER('BAT VwRptMspAssignment'[Start.Date])),
              'BAT VwRptMspAssignment'[EoaReason]="Successful Completion"),
  </code></pre>
  
  <p>'BAT VwRptMspAssignment'[FinishFlag])</p>
  
  <p>but neither of these solutions have worked.<br>
  Does anyone have any idea why or what else I can try to accomplish this? An example of the data format, exported to Excel:</p>
  
  <p><a href="https://i.stack.imgur.com/03Ieu.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/03Ieu.jpg" alt="scrubbed data"></a></p>
  
  <p>"Contractors Available.2" is the calculated column.
  Note the 521 in the first line. If I apply all of these filters in Excel, it should be zero, this job title is unique in the dataset. It says 107 "Technical Writer - Expert" rows should have ended within a month of 9/26/2017, but these are the only 3 technical writers in the dataset, and zero of the other two ended their assignments within a month of 9/30/2016:
  <a href="https://i.stack.imgur.com/TQDi8.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TQDi8.jpg" alt="enter image description here"></a></p>