title: Calculation of the absence rate per employee
body: <p>The goal is to use Dax and Power Pivot to determine what the absentee rate per employee is. This lowest granularity can be used later to determine the average absence per area.</p>
  
  <p>I get from the team leaders an overview (Excel), which is shown per day, whether the employee was there on the day or sick or on vacation</p>
  
  <p><strong>tblOverview</strong>
  <em>Date - Name - State - Area - Shift</em></p>
  
  <ul>
  <li>Date: Date from 01.01.- 31.12. </li>
  <li>Name: Name of the person </li>
  <li>State: A = present, E = absent </li>
  <li>Area: area </li>
  <li>Shift: Week shift or weekend shift (Mo + tue day off)</li>
  </ul>
  
  <p>Since the employees have different shift models, this is probably a somewhat more difficult task. Most work from Monday to Friday. However, some work from Wednesday to Sunday.</p>
  
  <p>Somehow I have to find out how many working days each employee has per week / month and how many days he has been absent. Only how best?
  Can this be elegantly solved using a Dax function without adjusting the structure of the table?</p>
  
  <p>I am happy about every contribution!</p>
  
  <p>Edit:</p>
  
  <p>The first pivot table shows how many days every employe have worked ("A") per week.
  The second pivot table shows the absence days ("E"):
  <a href="https://i.stack.imgur.com/w0u0a.png" rel="nofollow noreferrer">enter image description here</a></p>
  
  <p>The result should be shown in the same structure.
  First, the function should compare if the day for each employee is a working day in comparison to the shift model (either mo-fri or wed-sun) and then if the employee is off ("E"). If the employee is off, the availability should be 0% or the absence 100% for the respective day.</p>
  
  <p>Here you have an example table/ file:</p>
  
  <p><a href="https://www.mediafire.com/file/gcn30o9pvxzl1mv/Example_Absence.xlsx/file" rel="nofollow noreferrer">enter link description here</a> </p>