The formula for ETR requires that we identify a specific time period during which we wish to calculate the ETR. This is necessary in order to define which employees that have left the organization should be counted, as well as to determine the average number of employees during that time. For this recipe, we have simplified things and simply chosen to compute an annual ETR for the year 2019. Thus, we set the __Year variable to the value 2019 in the first line and then calculate the start date of our period, __StartPeriod, and end date of our period, __EndPeriod, to be the first day of 2019 and the last day of 2019 respectively.
We know that we want to calculate the average number of employees during our defined time period. To do this, we will take the average of the number of employees at the beginning of the time period and the number of employees at the end of the time period. To calculate the number of employees at the beginning of the time period, __BeginningEmployees, we use the COUNTROWS function to count the rows in our fact table, R01_Table, that have a Hire Date greater than or equal to the start of the chosen time period and a Leave Date greater than the end of the chosen time period. This assumes that each row in the employee table represents a unique employment instance.
The calculation for the number of employees at the end of our defined time, __EndingEmployees, is similar to the calculation for __BeginningEmployees. This time, however, our FILTER clause specifies employees that have a Hire Date less than or equal to the end of our chosen time period as well as either a Leave Date greater than the end of our chosen time period or have a value for Leave Date that equals our special value for employees still employed with the organization. In our case, we have a special value for Leave Date that is always greater than the time period within context, but this would not be the case for a special value such as 1/1/1900.
Calculating the number of lost employees, __LostEmployees, is again similar to the calculations for __BeginningEmployees and __EndingEmployees. This time our FILTER clause specifies employees that have a Leave Date within the period of time defined by our __StartPeriod and __EndPeriod variables.
Now that we have the components of our calculation, we can return the annual ETR by dividing the number of lost employees within the time period, __LostEmployees, by the average number of employees within the time period. The average number of employees within the time period is simply the sum of the number of employees at the beginning and end of the time period divided by two.