Forum Discussion

AartiD's avatar
AartiD
Helper 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.

     
mdaatifraza5556's avatar
mdaatifraza5556
Super User
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.