For this recipe, we assume that the organization's Total Annual Revenue, Total Annual Cost, and Employee Overhead Cost Factor are all known values. The Total Annual Revenue measure represents the total amount of revenue earned during the year under analysis. Similarly, the Total Annual Cost measure represents the total of all costs to the organization, including employee costs. The Employee Overhead Cost Factor measure represents the multiplier by which employee annual salaries are adjusted to account for employee benefits such as health care.
The next data point we need is the amount of revenue earned by the organization during the time period under analysis. This is the purpose of the Revenue measure. While the actual revenue earned per quarter may be known to the organization, we can approximate this revenue from the Total Annual Revenue measure. To accomplish this, we make the assumption that the amount of revenue earned per day is evenly distributed across each day of the year. Therefore, we start by getting the starting and ending dates of the period under review, __StartPeriod and __EndPeriod. We use this information to determine the number of days within the period in question, __DaysInPeriod, by using DATEDIFF to calculate the number of days between __StartPeriod and __EndPeriod. We then determine the year of our analysis, __Year, and use this to determine the total number of days within the year, __DaysInYear. We can then calculate the amount of revenue earned in the period by dividing the total annual revenue, Total Annual Revenue, by the number of days in the year, __DaysInYear, and then multiplying by the number of days in the period, __DaysInPeriod.
We also need the total costs incurred by the organization during the period in question, the Total Costs measure. This calculation is made by making the same assumption as was made with Total Annual Revenue and the equation is identical to our Revenue calculation, except that we use Total Annual Cost instead of Total Annual Revenue.
We now need to determine how many FTEs worked during the time period under analysis. This is the FTE measure. To do this, we start by getting the starting and ending dates of the period under review, __StartPeriod and __EndPeriod. We use this information to determine the number of days within the period in question, __TotalDaysInPeriod, by using DATEDIFF to calculate the number of days between __StartPeriod and __EndPeriod. We then 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 and the number of relevant days within that period.
We determine how many full-time employees are included in the __Employees table variable; this is our __FullTimeEmployees variable. To compute __FullTimeEmployees, we use FILTER to restrict the rows to only those employees whose __DaysInPeriod column equal the __TotalDaysInPeriod variable. We then count the number of rows using COUNTROWS. This number is stored in the __FullTimeEmployees variable.
Determining the FTEs for all employees that worked for a portion of the time period in question, __PartTimeEmployees, is similar to __FullTimeEmployees. We use FILTER to restrict the rows to those employees whose __DaysInPeriod column is less than the __TotalDaysInPeriod variable. We sum the values of the __DaysInPeriod column using SUMX and then divide this value by the number of days in the period, the __TotalDaysInPeriod variable. The final value for the FTE measure is simply the sum of the full-time employees, __FullTimeEmployees, and the part-time employees, __PartTimeEmployees.
The final component for calculating HVCA is to determine the cost of employees during the time period in question, the Employment Costs measure. The formula for Employment Costs starts out identically to the formula for FTE. However, we add an additional ADDCOLUMS function to the calculation of our __Employees table variable that adds the __TotalCostInPeriod column. This column spreads the annual cost of the employee, the Annual Salary column, over each day of the year, __DaysInYear, and then multiplies that value by the employee's __DaysInPeriod column. We can determine the final value for Employment Cost, __TotalCostInPeriod, by using SUMX to sum the __TotalCostInPeriod column within our __Employees table variable and then multiplying this sum by the Employee Overhead Cost Factor.
Finally, the HVCA measure can be calculated through the implementation of the HCVA formula using the Revenue, Total Costs, Employment Costs, and FTE measures.