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.

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

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]