First, it should be noted that this recipe assumes a seven-day work week. If this is not the case for your organization, see the Replacing Excel's NETWORKDAYS function recipe in Chapter 2, Dealing with Dates and Calendars.
We start by setting the start and end of our time period for analysis. This is done by setting the __StartPeriod variable to the minimum Date in the R02_Calendar table currently within the context, as well as setting the __EndPeriod variable to the maximum Date in the R02_Calendar table currently within the context. By setting these variables dynamically based upon the context, this allows the measure to work within any time interval chosen.
We know from our basic formula for absenteeism that we need the number of available days or hours. While this seems straightforward, we must take into account that employees may be hired or leave the organization during the time period under analysis. Thus, we must compute a table, __Employees, that accounts for employees coming into and leaving the organization during the time period in question. We do this by first using FILTER to get the employees that have a Hire Date before the end of our period, __EndPeriod, and have a Leave Date of after the start of our time period, __StartPeriod, or have a special Leave Date value that denotes that they are still employed by the organization, in this case, 1/1/9999.
We use ADDCOLUMNS to compute each employee's relevant starting and ending dates, __MinDate and __MaxDate, within the period in question. For __MinDate, we check if the employee's Hire Date is after the start of our period, __StartPeriod, and, if so, their Hire Date is used. Otherwise, the start of the period, __StartPeriod is used. Similarly, for __MaxDate, we check if the employee's Leave Date is before the end of our period, __EndPeriod, and, if so, their Leave Date is used. Otherwise, the end of the period, __EndPeriod is used. We then use ADDCOLUMNS again to create the __DaysInPeriod column, which is the number of days between __MaxDate and __MinDate. The __Employees variable now contains a table with only the employees relevant to the time period under analysis, as well as the number of relevant days within that period.
We can now calculate the total number of available days within the time period across all employees that worked during that time period, __TotalDaysInPeriod. We do this by simply using SUMX to sum the values in the __DaysInPeriod column within the __Employees table variable. Note that this method works whether one employee is within context or multiple employees are within context. If only one employee is within context, then the __Employees table variable will contain, at most, one employee.
Now that we have the denominator for our basic absenteeism calculation, __TotalDaysInPeriod, we can move on to calculating the numerator, the number of days of absence. To do this, we must determine the number of absent days within the time period in question. We must be concerned with three types of absence periods:
- Absences that begin and end within the time period in question
- Absences that begin prior to the start of the time period in question but end during the time period in question
- Absences that begin prior to the end of the time period in question but end after the time period in question
For the last two types of absences, we do not wish to count absent days that fall outside of the time period in question. To accomplish this, we start by using FILTER to filter our R02_Absent table. The first filter clause handles the first type of absence, those absences that begin and end within the time period in question. The second filter clause handles including those absences that start prior to the time period in question, __StartPeriod, but also have an AbsentReturnDate that is both after the __StartPeriod and before or equal to the __EndPeriod. The third clause handles our third type of absence, those absences with an AbsentStartDate that is both after or equal to the start of our period, __StartPeriod, and before or equal to the end of our period, __EndPeriod. In addition, these types of absences must have an AbsentReturnDate that is after the end of our period, __EndPeriod.
We use ADDCOLUMNS to compute each absence's relevant starting and ending dates, __AbsentStartDateInPeriod and __AbsentEndDateInPeriod, within the period in question. For __AbsentStartDateInPeriod, we check if the AbsentStartDate is before the start of our period, __StartPeriod, and, if so, __StartPeriod is used. Otherwise, the start of the absence, AbsentStartDate, is used. Similarly, for __AbsentEndDateInPeriod, we check if AbsentReturnDate is after the end of our period, __EndPeriod, and, if so, __EndPeriod is used. Otherwise, the end of the absence, AbsentReturnDate, is used. We then use ADDCOLUMNS again to create the __DaysAbsent column, which is the number of days between __AbsentEndDateInPeriod and __AbsentStartDateInPeriod. The __AbsentTable variable now contains a table with only the absences relevant to the time period under analysis as well as the number of relevant days of absence within that period.
We can now calculate the total number of absence days within the time period across all absences relevant during that time period, __TotalDaysAbsent. We do this by simply using SUMX to sum the values in the __DaysAbsent column within the __AbsentTable table variable.
We now have both the numerator and the denominator for our simple base absenteeism formula. Thus, we now simply divide __TotalDaysAbsent by __TotalDaysInPeriod to arrive at our absenteeism ratio, __AbsenteeismRatio. Since we wish to return zeros and not blanks, we make a quick check using ISBLANK and return 0 if true or __AbsenteeismRatio if false.