Rolling 3-Month Average in DAX

This document outlines a DAX function to compute the rolling 3-month average of a specified measure. The function will aggregate data over the last three months based on the current date context.

Function Definition

Purpose

Calculates the rolling average of a specified measure over the last three months period.

Parameters

• Measure: The measure for which the rolling average is calculated. This should be defined in your data model prior to using this function.
• DateColumn: The date column that the function depends on to determine the rolling window.

Return Type

Returns a decimal number representing the average value of the specified measure over the last three months.

Exceptions

• Throws an error if the date column is not found in the data model.

Code Implementation

DAX Code Snippet

Rolling3MonthAverage = 
VAR CurrentDate = MAX('Date'[Date])  // Get the current date context
VAR FirstDate = EDATE(CurrentDate, -3) // Calculate the date three months ago
RETURN
    AVERAGEX(
        FILTER(
            ALL('Date'), // Remove any existing filters on the Date table
            'Date'[Date] <= CurrentDate && 
            'Date'[Date] > FirstDate // Filter to include only the last three months
        ),
        [YourMeasure] // Replace with your defined measure
    )

Explanation of Code

1. Variable CurrentDate: Captures the current context date using MAX on the Date column.
2. Variable FirstDate: Calculates the date which is three months prior to the current date using EDATE.
3. Return Statement:
   • Uses AVERAGEX to calculate the average of the measure over the filtered date range.
   • FILTER removes any pre-existing filters on the Date table and restricts the date range to the last three months.

Input Validation

• Ensure that the measure used in the AVERAGEX function is correctly defined in your model.
• Confirm that the Date column passed as a filter exists within your dataset.

Usage Example

To illustrate how the function can be used in a report or dashboard, let's assume you have a measure called Total Sales that represents sales figures. You can implement the function in your report like so:

Rolling Sales Average = 
Rolling3MonthAverage([Total Sales], 'Date'[TransactionDate])

Where:

• [Total Sales] is the measure containing sales data.
• 'Date'[TransactionDate] is the date column corresponding to each transaction.

Conclusion

This DAX function efficiently computes a rolling 3-month average of a specified measure, providing valuable insights for time-series analysis. For advanced scenarios or further assistance, consider exploring courses available on the Enterprise DNA Platform which cover DAX and analytical techniques in depth.