Rolling Average Calculation in DAX

Rolling Average Calculation in DAX

Overview

The provided code snippet calculates a "Rolling Average" using Data Analysis Expressions (DAX) in Power BI or Analysis Services. This calculation produces the average of values over a specified rolling window, which in this case is the last 7 days.

Code Breakdown

Rolling Average =
AVERAGEX(
    CALCULATE(
        SUM('Table'[Value]),
        DATESBETWEEN('Table'[Date], MAX('Table'[Date]) - 6, MAX('Table'[Date]))
    ),
    'Table'[Value]
)

Explanation of Each Component

1. AVERAGEX (Outer Function):

   • Purpose: Calculates the average of an expression evaluated over a table. In this case, it computes the average of values for the specified date range.
   • Syntax: AVERAGEX(<table>, <expression>)

2. CALCULATE (Inner Function):

   • Purpose: Modifies the context of the data (typically filtering) to calculate the sum within a specific date range.
   • Syntax: CALCULATE(<expression>[, <filter1> [, <filter2> [, ...]]])
   • Context in the Code: The SUM('Table'[Value]) expression is calculated within the date range context defined by DATESBETWEEN.

3. SUM ('Table'[Value]):

   • Purpose: Aggregates the total sum of the 'Value' column.
   • Syntax: SUM(<column>)
   • Context in the Code: Used within CALCULATE to specify the values needing summation.

4. DATESBETWEEN Function:

   • Purpose: Returns a table with a column of dates between a specified start date and end date.
   • Syntax: DATESBETWEEN(<dates>, <start_date>, <end_date>)
   • Parameters:
     • 'Table'[Date]: The date column used for filtering.
     • MAX('Table'[Date]) - 6: Start date, which is six days before the current (latest) date.
     • MAX('Table'[Date]): End date, which is the current (latest) date.
   • Context in the Code: Defines the 7-day rolling window.

Overall Functionality

The calculation works in steps as follows:

1. Date Filtering: DATESBETWEEN('Table'[Date], MAX('Table'[Date]) - 6, MAX('Table'[Date])) creates a date range from the current date back to six days prior.
2. Aggregated Value: CALCULATE(SUM('Table'[Value]), ...) computes the sum of 'Value' within the filtered date range.
3. Rolling Average Calculation: AVERAGEX then averages these summed values, giving us the rolling average over the last 7 days.

Additional Example

For a broader understanding, here is a simplified example using a 3-day window:

ThreeDayRollingAvg =
AVERAGEX(
    CALCULATE(
        SUM('Sales'[Amount]),
        DATESBETWEEN('Sales'[Date], MAX('Sales'[Date]) - 2, MAX('Sales'[Date]))
    ),
    'Sales'[Amount]
)

In this example:

• We compute the rolling average over 3 days.
• The Sales table’s Amount column is used instead of Table and Value.

Conclusion

Understanding each part of the DAX code and its role in the overall calculation helps demystify how rolling averages are computed. This approach can be adapted to various rolling windows and datasets to perform similar calculations. For further learning, Enterprise DNA Platform provides extensive courses on DAX and data analysis techniques.