Prorating a value by using annual intensity

Edit online

Learn how to calculate prorated energy consumption by using captured floor area, intensity, and record duration.

About this task

Some data capture approaches require prorating an annual intensity or rate value for the period of the record that is being captured.

For example, an account style might capture floor area in the Area (square ft) field in C_21 and an annual intensity metric in the Annual energy intensity (kWh/sq ft) field in C_22 to estimate energy consumption in the Natural Gas (kWh) field in C_12.

To calculate the kWh of natural gas consumed for a billing period, create an assignment rule to multiply the area by the intensity by the fraction of the year that the record covers. The rule evaluation populates the Natural Gas (kWh) field in C_12, where C_12 represents the kWh of natural gas consumed.

Procedure

Use the following steps to build up the assignment rule for calculating a prorated energy consumption value:

  1. Use the DATEDIFF function to calculate the number of days between the Start_Period and the End_Period of the record:
    DATEDIFF(DAY, Start_Period, End_Period)
    If Start_Period = 2024-01-01 and End_Period = 2024-12-31, DATEDIFF(DAY, Start_Period, End_Period) returns 364.
  2. Add one to the result of the DATEDIFF function to ensure that the full duration includes both start and end dates.
  3. Use the following equation to normalize the period length to a fraction of a year:
    (DATEDIFF(DAY, Start_Period, End_Period) + 1)/365
    • If the period spans the entire year, the fraction is 365/365, which equates to 1.
    • If the period is 1 month, for example, January, the fraction is 31/365, which equates to 0.0849.
  4. To complete the equation, multiply the floor area and the intensity by the normalized period length.
    You can apply the following formula to calculate the prorated energy consumption in C_12:
    C_12 = C_21 * C_22 * ((DATEDIFF(DAY, START_PERIOD, END_PERIOD) + 1) / 365)