DAX Formula for Forecasting Hours on a Monthly Basis

Objective

To create a DAX formula that forecasts hours on a monthly basis based on actual hours logged in previous periods.

Formula Explanation

The formula will utilize the SUM function to aggregate the actual hours from a table and DATEDIFF or a similar time intelligence function to project future hours based on historical data trends. A common method for forecasting is to use the AVERAGEX function to find the average of the past months' hours and then project that average for future months.

DAX Formula

Forecasted Hours = 
VAR CurrentMonth = MAX('HoursData'[Date].[Month])
VAR PriorMonthsAverage = 
    AVERAGEX(
        DATESINPERIOD('HoursData'[Date], 
        EDATE(CurrentMonth, -6), 
        6, 
        MONTH), 
        'HoursData'[Actual Hours]
    )
RETURN 
    IF(
        ISBLANK(PriorMonthsAverage), 
        BLANK(), 
        PriorMonthsAverage
    )

Breakdown of the Formula

1. Variables Declaration:

   • CurrentMonth: Captures the latest month available in the 'HoursData' table.
   • PriorMonthsAverage: Calculates the average of the actual hours for the last six months.

2. AVERAGEX Function:

   • Iterates through the last six months identified by DATESINPERIOD.
   • Uses Actual Hours data to compute the average.

3. Return Value:

   • The IF statement checks if PriorMonthsAverage returns any value.
   • If it's blank (meaning there aren't enough historical data to base the forecast on), it returns BLANK(), otherwise, it returns the calculated average.

Practical Example

Assuming you have a table named HoursData with the following structure:

• Using the above formula, if we are in August 2023, the formula will calculate the average of the last six months of actual hours (120, 130, 110, 140, 150, 160, 170).
• The expected result for forecasted hours for August 2023 would be (120 + 130 + 110 + 140 + 150 + 160 + 170) / 6 = 143.33 (approximately).

Conclusion

This DAX formula serves to project future monthly hours based on recorded historical data. The use of AVERAGEX ensures a simple yet effective forecast based on trends, allowing data analysts to derive actionable insights for planning and resource allocation.

For further skills improvement and comprehensive learning in DAX, consideration should be given to the courses available on the Enterprise DNA Platform.