How to Calculate Monthly Average in Power BI

Calculating a monthly average is one of the most common tasks you’ll perform in Power BI, yet it’s the key to unlocking clearer insights from your data. Looking at daily fluctuations can be confusing, but averaging performance by month smooths out the noise and helps you spot genuine trends. This tutorial will walk you through a clear, step-by-step process for calculating monthly averages in Power BI using DAX.

Why Calculating a Monthly Average Matters

Daily performance metrics, like sales or website traffic, can swing wildly. A great Tuesday might be followed by a slow Wednesday, making it hard to see the bigger picture. When you average your data on a monthly basis, you get several benefits:

• Smoother Trends: Averages remove the daily "noise," making it easier to see if your performance is trending up, down, or holding steady over time.
• Better Comparisons: It's more meaningful to compare this month's average performance to last month's, or to the same month last year, than it is to compare one random Tuesday to another.
• Realistic Benchmarking: A monthly average gives you a stable benchmark to measure future performance against, helping you set realistic goals and KPIs.

Imagine you're tracking daily sales. One day, you hit a record $10,000, but the next, it’s only $1,500. It’s difficult to plan based on this volatility. A monthly average sales figure, however, might show you consistently perform at around $4,500 per day on average for the month, which is a much more reliable number for forecasting and budget planning.

Step 1: Prepare Your Data with a Date Table

Before you write a single DAX formula, the most important step is to set up a proper calendar or date table. While Power BI can often work with dates in your main data table (like a sales or orders table), it's a strongly recommended best practice to use a dedicated date table for all time intelligence calculations.

A date table is a separate table that contains a continuous list of dates and associated time periods (like year, quarter, month, and day of the week). Connecting this to your data table unlocks the full power of Power BI's time-based analysis functions.

How to Create a Date Table with DAX

If you don't have a date table from your data source, you can create a powerful and dynamic one directly in Power BI with a few lines of DAX code. This ensures your calendar always covers the full date range of your data.

1. In Power BI Desktop, navigate to the Data view (the table icon on the left panel).
2. Go to the Modeling tab in the top ribbon and click New Table.
3. A formula bar will appear. Copy and paste the following DAX code into it and press Enter:

Date = ADDCOLUMNS(   CALENDAR(MIN(Sales[OrderDate]), MAX(Sales[OrderDate])),   "Year", YEAR([Date]),   "Quarter", "Q" & FORMAT([Date], "Q"),   "MonthNum", MONTH([Date]),   "Month", FORMAT([Date], "mmmm"),   "YearMonth", FORMAT([Date], "yyyy-mm") )

Note: Replace Sales[OrderDate] with the actual name of the date column in your main data table. This formula uses the earliest and latest dates from your data to automatically set the range for your calendar.

Mark as Date Table and Create a Relationship

Once your date table is created, you need to tell Power BI what it is and connect it to your other data.

1. With the new Date table still selected, go back to the Modeling tab and click on Mark as date table.
2. In the dialog box that appears, select the [Date] column and click OK.
3. Finally, go to the Model view (the icon at the bottom of the left panel). Find your new Date table and your main data table (e.g., Sales). Drag the Date column from your Date table and drop it onto the corresponding date column (e.g., OrderDate) in your Sales table to form a “one-to-many” relationship.

With this setup, you are ready to start calculating monthly averages effectively.

Step 2: Calculate the Monthly Average with DAX

Now we'll create a DAX "measure" to calculate the average. Measures are dynamic calculations that respond to filters and selections in your report. We'll start with a simple measure for your base metric (like total sales) and then build the monthly average on top of it.

First, Create a Base Measure

It's always a good idea to have an explicit measure for simple summaries, even if you can drag and drop a field into a visual. This makes your more complex formulas easier to read and manage.

1. Right-click on your Sales table (or whichever table holds the numbers you want to analyze) and select New Measure.
2. In the formula bar, type the following:

Total Revenue = SUM(Sales[Revenue])

Replace Sales[Revenue] with the table and column name you want to analyze. Press Enter.

Second, Calculate the Monthly Average

Now we’ll use the AVERAGEX function. This function iterates through a table (in our case, a list of months) and evaluates an expression (our [Total Revenue] measure) for each row, then returns the average of the results. This is exactly what we need for a monthly average.

1. Right-click on your Sales table again and select New Measure.
2. Enter this DAX formula:

Monthly Average Revenue = AVERAGEX(   VALUES('Date'[YearMonth]),   [Total Revenue] )

Breaking Down the Formula:

• AVERAGEX: The iterator function that calculates an average.
• VALUES('Date'[YearMonth]): This part of the formula provides the table for AVERAGEX to iterate over. VALUES returns a unique list of all YearMonth values in the current filter context. So, if your visual is filtered for the year 2023, this will return a list like "2023-01", "2023-02", "2023-03", and so on.
• [Total Revenue]: This is the expression that gets calculated for each month in the list. Power BI calculates the total revenue for January, then February, then March, and finally AVERAGEX averages those monthly totals.

Step 3: Using Your Average in Visualizations

Now that you have your measure, you can use it to build insightful visuals.

Show the Overall Average in a Card

The simplest way to use your measure is with a Card visual. Drag the Card visual onto your report canvas and then drag your [Monthly Average Revenue] measure into the "Fields" area. This will show you the overall average monthly revenue across the entire timespan selected in your report filters.

Combine Monthly Totals and Average in a Chart

A line or column chart is perfect for comparing each month's performance to the overall average.

1. Create a Line and Stacked Column chart.
2. Drag the Month or YearMonth from your Date table into the "Shared axis", and your [Total Revenue] measure into "Column values". Drag your [Monthly Average Revenue] measure into "Line values". This will show your revenue changing each month with a line representing the running average.

Alternatively, Power BI has a built-in feature to create this line automatically, which keeps your chart cleaner.

1. Create a Column chart again, using Month in the axis and [Total Revenue] as the value.
2. Select the visual, then go to the Format visual pane (the paintbrush icon).
3. Look for the Analytics tab (the magnifying glass icon).
4. Expand the Average line section and click + Add line. This will automatically calculate and display the average across all the months shown in your chart.

Handling Averages for Partial Months

A common issue is that the current, incomplete month can skew your overall average down. If it’s only the fifth day of the month, its total revenue will be very low compared to full months. You might want to exclude it from the average calculation.

You can create a slightly more advanced version of your average measure that only includes full, completed months.

1. In your Date table, create a new calculated column:

IsCompleteMonth = IF(   EOMONTH('Date'[Date], 0) < TODAY(),   TRUE(),   FALSE() )

This formula checks if the end of the month for any given date is before today. If it is, that date belongs to a completed month.

1. Now you can create a new version of your average monthly measure, filtered by this new column.

AvgRevenue (CompleteMonths) = CALCULATE([Monthly Average Revenue], 'Date'[IsCompleteMonth] = TRUE())

Using this new measure in your cards and charts will provide you with your average based solely on full periods of performance, which often provides much more accurate insight.

Final Thoughts

Calculating monthly averages in Power BI is transformative. By establishing a proper date table and using DAX iterator functions like AVERAGEX, you can smooth out data and unlock more reliable performance trends. This fundamental skill empowers you to move past daily fluctuations and make more strategic decisions based on clear, long-term signals.

We designed Graphed to eliminate this entire process. Instead of writing DAX, creating data models, and building visuals manually, you simply connect your data sources and ask, "What is my monthly average revenue by country?" in plain English. Instantly, build a live dashboard that answers your questions, saving you hours from data wrangling so you can focus on acting on your insights.