DAX Monthly Average Calculation

DAX Formula for Calculating Monthly Average

Formula Creation

To calculate the monthly average in DAX, you typically need to aggregate your data by month first and then find the average of these monthly aggregates. The AVERAGEX function is useful for this calculation.

DAX Code

MonthlyAverage = 
AVERAGEX(
    SUMMARIZE(
        Sales, 
        Sales[YearMonth],  -- Replace with your date field aggregated to month level
        "MonthlyTotal", SUM(Sales[SalesAmount])  -- Replace SalesAmount with your measure field
    ),
    [MonthlyTotal]
)

Explanation

1. SUMMARIZE Function:

   • The SUMMARIZE function groups data by a specified column, Sales[YearMonth] in this case. YearMonth should be a column that uniquely identifies each month.
   • Inside the SUMMARIZE function, the expression SUM(Sales[SalesAmount]) calculates the total sales for each month.

2. AVERAGEX Function:

   • AVERAGEX iterates over the table produced by SUMMARIZE and calculates the average of the MonthlyTotal.

Practical Illustration

Assume you have a table named Sales with the following columns:

• SalesDate: Date of the Sale
• SalesAmount: Amount of the Sale

If you have a Year-Month column (YearMonth) created using a date table, the DAX formula for calculating the monthly average sales would be structured as shared above. This formula gives you the average of monthly totals across all months in your dataset.

Further Learning

To deepen your understanding of DAX and enhance your data modeling skills, consider exploring Enterprise DNA's comprehensive training modules.