DAX Formula for Forecasting Hours Using CAGR

Objective

To create a DAX formula that forecasts future hours based on actual hours using the Compound Annual Growth Rate (CAGR) method. This allows for an estimation of future hours based on historical data.

CAGR Formula Definition

The CAGR can be calculated with the formula: [ \text{CAGR} = \left( \frac{\text{Ending Value}}{\text{Beginning Value}} \right)^{\frac{1}{n}} - 1 ] Where:

• Ending Value: The value at the end of the period (most recent actual hours).
• Beginning Value: The value at the start of the period (initial actual hours).
• n: The number of years (or periods) between the two values.

DAX Implementation

To estimate future hours using CAGR, you can use the following DAX measure:

ForecastedHours = 
VAR Start_Value = 
    CALCULATE(
        SUM(TableName[ActualHours]), 
        FILTER(TableName, TableName[Year] = MIN(TableName[Year]))
    )
VAR End_Value = 
    CALCULATE(
        SUM(TableName[ActualHours]), 
        FILTER(TableName, TableName[Year] = MAX(TableName[Year]))
    )
VAR Periods = 
    CALCULATE(
        DISTINCTCOUNT(TableName[Year]), 
        FILTER(TableName, TableName[Year] >= MIN(TableName[Year]) && TableName[Year] <= MAX(TableName[Year]))
    )

VAR CAGR = 
    DIVIDE(End_Value, Start_Value) ^ (1 / Periods) - 1

RETURN
    End_Value * (1 + CAGR) ^ DesiredFutureYears

Explanation of the Formula

1. Variables (VAR):

   • Start_Value: Calculates the total actual hours at the beginning of the period (first year).
   • End_Value: Calculates the total actual hours at the end of the period (last year).
   • Periods: Counts the number of distinct years between the start and end values.

2. CAGR Calculation:

   • The CAGR is computed using the formula described above. DIVIDE(End_Value, Start_Value) computes the ratio of the ending to starting value, and raising it to the power of 1 / Periods gives the CAGR.

3. Forecast Calculation:

   • Finally, the formula forecasts future hours by multiplying the End_Value with the CAGR raised to the power of the number of desired future years (DesiredFutureYears).

Example Implementation

Consider a table named HoursTable with the following columns:

• Year
• ActualHours

If the historical years are 2020 to 2023, with the associated actual hours being:

• 2020: 1000
• 2021: 1200
• 2022: 1500
• 2023: 1800

To forecast hours for 2 future years (2024 and 2025), apply the formula to the dataset, using the desired future years as 2.

Output

The output will give you the forecasted hours for 2024 and 2025 based on the calculated CAGR from the actual hours data.

Conclusion

This DAX measure provides a robust method for estimating future hours based on historical data using CAGR. Proper implementation of this formula will facilitate better planning and decision-making based on forecasted performance. For further enhancement of your DAX skills, consider exploring the Enterprise DNA Platform for course offerings that delve deeper into such calculations.