Hi AartiD ,

This is a classic scenario - you want the average of completed months but exclude the current month since it's not finished yet.

Here's the exact solution you need:

Avg Monthly Sales (Excluding Current) = 
VAR CurrentMonth = EOMONTH(TODAY(), 0)
RETURN
AVERAGEX(
    FILTER(
        VALUES('Date'[Month]),
        EOMONTH('Date'[Month], 0) <> CurrentMonth
    ),
    CALCULATE(SUM(Sales[Amount]))
)

What this does:

• Gets all months that have data
• Filters out the current month (June in your case)
• Calculates average sales for only the remaining months
• Divides total sales by number of complete months (5 in your example)

Alternative simpler version:

Avg Monthly Sales = 
CALCULATE(
    AVERAGEX(
        VALUES('Date'[Month]),
        [Total Sales]
    ),
    'Date'[Month] <> FORMAT(TODAY(), "yyyy-mm")
)

How it works with your data:

• Jan: 100, Feb: 0, Mar: 200, Apr: 500, May: 0
• Total: 800, Complete months: 5
• Average: 800 ÷ 5 = 160

When July data comes in, June automatically gets included in the calculation and July becomes the excluded current month.

This approach counts all previous months in the calculation whether they have sales or not, which matches exactly what you described.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

This response was assisted by AI for translation and formatting purposes.

Hi AartiD , 

Steps can be followed to get the desired average :

Step 1: Complete Date Table

Create or mark a Calendar table that contains all months in the period, not just the months where you have sales data. This ensures you count all months (including blanks).

Step 2: DAX Measure for Avg Monthly Sales (Exclude Current)

• A Calendar table named 'Calendar' with a column [Month] and [Year]

• Your fact table named 'Sales' with columns [Month], [Year], and [Sales]

• Both tables related by Month and Year
  
  

DAX 
Avg Monthly Sales (Excluding Current) =
VAR CurrentMonth =
    MAX('Calendar'[Month])
VAR CurrentYear =
    MAX('Calendar'[Year])
VAR MinMonth =
    CALCULATE(MIN('Calendar'[Month]), ALL('Calendar'))
VAR MinYear =
    CALCULATE(MIN('Calendar'[Year]), ALL('Calendar'))
VAR MonthsToInclude =
    FILTER(
        ALL('Calendar'),
        ('Calendar'[Year] < CurrentYear
         || ('Calendar'[Year] = CurrentYear && 'Calendar'[Month] < CurrentMonth))
    )
VAR TotalMonths =
    COUNTROWS(MonthsToInclude)
VAR TotalSales =
    CALCULATE(
        SUM('Sales'[Sales]),
        MonthsToInclude
    )
RETURN
    DIVIDE(TotalSales, TotalMonths)

 
⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore More]

Hi AartiD 

Just following up on your request regarding calculating the average of sales by month excluding the current month (June in this case) — and dynamically shifting when new data (e.g., July) arrives.

burakkaragoz mdaatifraza5556 GrowthNatives  have already shared responses with DAX solutions and a sample report file that demonstrates this behavior. The logic accounts for missing sales data in certain months and ensures the average is calculated based on the correct number of months.

Please have a look at their solutions and let us know if it addresses your requirement or if any part needs further clarification.