Forum Discussion

AartiD's avatar
AartiD
Icon for Helper II rankHelper II
1 year ago
Solved

Average for Month Excluding Current Month

I want ,average of months excluding current month (In our case right now current month is June)  in Power BI irrespective of sales amount missing in any specific month in Power BI. When July data is ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • mdaatifraza5556's avatar
    mdaatifraza5556
    1 year ago

    Hi AartiD 

    Can you please try the below dax ?

     

    AverageExcludingCurrentMonth =
    VAR CurrentMonth =
        CALCULATE(
            MAX('Table'[Date]),
            REMOVEFILTERS('dim_date')
        )
    VAR CurrentMonthStart = DATE(YEAR(CurrentMonth), MONTH(CurrentMonth), 1)
    VAR FilteredMonths =
        FILTER(
            ALL(dim_date),
            dim_date[Date] < CurrentMonthStart
        )
    VAR TotalMonths =
        COUNTROWS(
            SUMMARIZE(
                FilteredMonths,
                'dim_date'[Month]
            )
        )
    VAR TotalSales =
        CALCULATE(
            SUM('Table'[SalesAmount]),
            FilteredMonths
        )
    RETURN
        DIVIDE(TotalSales, TotalMonths)
     
     
    1. case when current month is jun

     

    2. case when current month is jul
     

     

     
    You Can explore the Pbix file how it work by downloading the .pbix file
     
     

    IF this answers yours questions, kindly accept it as a solution and give kudos.

     
burakkaragoz's avatar
burakkaragoz
Icon for Super User rankSuper User
1 year ago

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.