Do you want to calculate a monthly average in Power BI?

As a data analyst, calculating monthly averages is a crucial aspect of extracting insights from your datasets. Relying on the versatility of Power BI, you can effortlessly distill complex data points into meaningful summaries.

Whether you’re an experienced data analyst or just beginning your analytical journey, mastering the computation of monthly averages in Power BI empowers you to uncover valuable insights that inform effective decision-making.

This article will highlight several effective methods to calculate the monthly average from your dataset in Power BI and help you gain valuable insights that will enhance your reporting capabilities.

Calculate Monthly Averages Using the DIVIDE Function

When you need to calculate an index in Power BI, it normally requires the formulation of various measures. These measures are then translated into appropriate visualizations to effectively represent the data. Calculating monthly averages is no different.

In the following illustrations, the primary focus will involve generating a monthly sales average and subsequently visualizing this data using the matrix visual.

This first illustration involves using the DIVIDE function. But before that, the Total Sales measure will be created. This can be done by following these steps.

Sales = 
SUMX (
    FactInternetSales,
    FactInternetSales[UnitPrice] * FactInternetSales[OrderQuantity]
)

Step 1 – Create a Total Sum Measure

While there are different ways to achieve a monthly average, it’s usually more efficient to calculate the total for the index of interest. Hence, a need to calculate the total sum of sales. To get the total sales for this illustration, the SUMX function is used.

The reason the SUMX function is well-suited for use in this context is that the FactInternetSales table doesn’t have a revenue column. Being an iterator, the SUMX function eliminates the necessity to create an additional column.

Rather, it calculates the cumulative result of multiplying values from the FactInternetSales[OrderQuantity] and FactInternetSales[UnitPrice] columns on a row-by-row basis.

It’s worth noting that when the table has a column containing revenue information, the SUM function would serve the purpose adequately.

Step 2 – Get the Monthly Average

Now that you have the cumulative sum, you can easily return the monthly average for the metric of interest, which is the monthly average sales in this case. To do this, you can use the DIVIDE function.

Monthly Sales Average (DIVIDE) = 
DIVIDE (
    [Total Sales],
    COUNT (
        DimDate[EnglishMonthName]
    )
)

For the DIVIDE function to return the monthly sales average, use this DAX statement. The DIVIDE function has three arguments – <Numerator>, <Denominator>, and <AlternateResult>. The <AlternateResult> argument tells the DIVIDE function what to return when the denominator is zero.

In this syntax, the [Total Sales] measure is the <Numerator>, and the COUNT(DimDate[EnglishMonthName] expression is the <Denominator>.

The COUNT(DimDate[EnglishMonthName] returns the total number of cells containing month names in the DimDate[EnglishMonthName] column.

With this calculation, each month’s [Total Sales] is divided by the number of times that month appeared in the DimDate[EnglishMonthName] column. In other words, [Total Sales] per month is divided by the number of days in the month defined by the current filter context.

Based on this filter context, the monthly average for each month in a year can be returned as visible in the above image. By dividing each month’s Total Sales value by the number of days in the month, the corresponding Monthly Sales Average (DIVIDE) value is derived.

Monthly Sales Average (/) = [Total Sales] / COUNT ( DimDate[EnglishMonthName] )

Another syntax you can use is to divide with the DAX’s divide operator ( / ). Either one of the syntaxes will return the same result.

Calculate Monthly Averages Using Time Intelligence Functions

Time intelligence functions are powerful DAX functions that are effective for manipulating data using periods, including days, months, quarters, and years. They can be used to create calculations that compare and aggregate data over periods.

There are many time intelligence functions in DAX. However, for returning a monthly average calculation, the DATESBETWEEN, DATESINPERIOD, DATESMTD and TOTALMTD are most appropriate.

DATESBETWEEN

The DATESBETWEEN function returns a table containing the dates between two specified dates.

It has the following syntax:

DATESBETWEEN ( <Dates>, <StartDate>, <EndDate> )

• <Dates> – This takes a column containing dates.
• <StartDate> – the first date of the period.
• <EndDate> – the last date of the period.

Monthly Average (DATESBETWEEN) = 
    CALCULATE (
        [Total Sales],
        DATESBETWEEN (
           FactInternetSales[OrderDate],
            MIN (
                FactInternetSales[OrderDate]
            ),
            MAX (
                FactInternetSales[OrderDate]
            )
        )
    ) / COUNT ( DimDate[EnglishMonthName] )

With this DAX statement, you can return the monthly average result. This measure uses three different functions – CALCULATE, DATESBETWEEN, MIN, MAX, and COUNT. Here’s a breakdown of each part of the syntax.

1. CALCULATE – The CALCULATE function allows you to modify the context in which a measure is calculated by applying filters and conditions to the calculation. Its syntax is CALCULATE ( <Expression>, [, <Filter>,  [, <Filter> [, … ] ] ] ). You can add more than one filter condition to the CALCULATE function.

In this syntax, the [Total Sales] measure is the <Expression> while the DATESBETWEEN function is the only <Filter> argument.

2. DATESBETWEEN – This part of the syntax defines the filter condition. This condition defines the date range for which the [Total Sales] measure evaluates. In the syntax, the DATESBETWEEN function uses the syntax ( FactInternetSales[OrderDate], MIN ( FactInternetSales[OrderDate] ), MAX ( FactInternetSales[OrderDate] ).

The FactInternetSales[OrderDate] is the column containing the dates. The MIN ( FactInternetSales[OrderDate] ) and MAX ( FactInternetSales[OrderDate] ) refer to the first and last date based on the current filter context.

3. COUNT – returns the number of rows containing non-blank values which then divides the value returned by the CALCULATE syntax.

When the measure is added to the visual, it will return the monthly average value for each year in the filter context.

DATESINPERIOD

The DATESINPERIOD function returns a table containing the dates within a specified period. The DATESINPERIOD function’s syntax is as follows:

DATESINPERIOD ( <Dates>, <StartDate>, <NumberOfInterval>, <Interval> )

• <Dates> – references a column containing dates.
• <StartDate> – the date that determines the beginning of the period.
• <NumberOfIntervals> – the number of intervals to move backwards or forwards from the <StartDate>.
• <Interval> – the interval that defines the period. This could be a daily, monthly, quarterly, or yearly interval.

Monthly Average (DATESINPERIOD) = 
    CALCULATE (
        [Total Sales],
        DATESINPERIOD (
            DimDate[FullDateAlternateKey],
            MAX (
                DimDate[FullDateAlternateKey]
            ),
            -1,
            MONTH
        )
    ) / COUNT ( DimDate[EnglishMonthName] )

Just like the DATESBETWEEN syntax, this also uses the CALCULATE function to return a filtered calculation of the [Total Sales] measure. However, this filter in this CALCULATE is defined using the DATESINPERIOD function.

In the DATESINPERIOD syntax, The DimDate[FullDateAlternateKey] refers to the date table. The MAX(DimDate[FullDateAlternateKey]), refers to the start date for the period based on the current filter context.

And the -1 and MONTH arguments define the date period. These arguments tell the function to start on the last date in the date column and count 1 month back. All of these are then divided by the COUNT statement.

When you add the measure to the syntax, you can see the returned values are similar, especially at the month level.

At the year level, the difference in values is evident. This difference occurs because of the way the DATESINPERIOD function handles dates.

The DATESINPERIOD function shifts the date over a 30-day monthly period for every date in the filter context. The [Total Sales] value on the last day of the month is then divided by the number of days in the month.

The values on the year filter are different in the DATESINPERIOD measure because the year filter represents a position that has no month filter. For this reason, the DATESINPERIOD syntax defaults to the last day of the month for the year defined by the filter context.

In this case, the last month is December 2011, 2012, and 2013. For 2014, the last day is January. For the years 2011, 2012, and 2013, the yearly average value is a result of dividing the December 31 total sales value by the total number of days in the year – 365.

For the January 2014 value, the total sum value on the last day represents a 30-day sum which is divided by 30 days. This same value is divided by 365 for the 2014 average, hence the reason why the value differs from that derived from the other measures.

The values in the Total row are divided by the total number of days in the FactInternetSales[OrderDate] column. For the DATESINPERIOD measure, 50840.63 is divided by 3652. In the other measures, the numerator is the total number of sales made between December 2011 and January 2014.

DATESMTD and TOTALMTD

The DATESMTD function returns a table containing dates that begin on the first day of the month up to the last date of the month or as specified by the filter context. DATESMTD takes just one argument – <Dates> – which references a column containing dates.

Another function that works the same as the DATESMTD is the TOTALMTD function. Its syntax is like using a DATESMTD function as a CALCULATE filter modifier.

TOTALMTD ( <Expression>, <Dates>, [, Filter] )

Both functions can be used to return the monthly average in the same way.

Monthly Average (DATESMTD) = 
    CALCULATE (
        [Total Sales],
        DATESMTD (
            FactInternetSales[OrderDate]
        )
    ) / COUNT ( DimDate[EnglishMonthName] )

Monthly Average (TOTALMTD ) = 
    TOTALMTD (
        [Total Sales],
        DimDate[FullDateAlternateKey]
     ) / COUNT ( DimDate[EnglishMonthName] )

For both functions, they work the same way. The functions calculate the [Total Sales] value depending on the current date set by the filter context and divide it by the number of days in the month. This then returns the average monthly sales value.

While all the monthly average values are similar across measures, you can see the yearly averages differ. This is also because the DATESMTD and TOTALMTD functions cumulate values up to the last day of the month as specified in the filter context just like in the measure created using the DATEINPERIOD function.

However, DATESMTD and TOTALMTD begin this cumulation from the first day of the month up until the last day of the month. Each month’s cumulation begins on the first day of the month. For the DATESINPERIOD measure, the cumulation occurs over 30 days.

In the image, you can see how the values for the first day in January differ across the three functions. While the DATESINPERIOD function shows a 30-day sum, the MTD functions only show values for that particular date.

Nevertheless, the monthly average returned by the measures are calculated by dividing the value on the last day of the month by the number of days in the month as specified by the COUNT function in their syntaxes.

For the value in the total row, the total sales as of January 28 is divided by 3652.

Conclusions

Using monthly averages is an effective way to track progress over time and uncover hidden trends. To do this in Power BI using DAX, a comprehensive date table covering all relevant dates is necessary. Once you have that, you can create measures to achieve your desired results.

There are various approaches to calculating the monthly average. The key is to have a clear understanding of your objective.

When utilizing time intelligence functions, it is crucial to comprehend how they shift periods over time so you can have a firm grasp of the returned results and the calculation process.

However, if you prefer to simplify the process and avoid the complexities of comprehending the reasoning behind time intelligence results, the DIVIDE function serves as a convenient alternative.