cancel
Showing results for
Did you mean:
Frequent Visitor

## Average of 3 Months

Hello,

I have a calculated column:

```SalesMTD =
VAR RowDate = DimDate[Date]
RETURN
CALCULATE (
SUM ( FactInternetSales[SalesAmount]),
FILTER
All(DimDate[Date]),
DimDate[Date] <= RowDate
&& MONTH ( DimDate[Date] ) = MONTH ( RowDate )
)
)```

DimDate is my date table, and FactInternetSales[SalesAmount] comes from the AdventureWorks sample. I have MTD data for three years, and I want to get the average of three months each year; that is, I want July 2012, July 2013, and July 2014 MTD's averaged together. This is driving me nuts. Does anyone know how to do this?

Thanks,

Howard

7 REPLIES 7
Super User

## Re: Average of 3 Months

Hi,

Share some data and also show the expected result.

Frequent Visitor

## Re: Average of 3 Months

Hello,

Here is some data:

```Date                  MTD           Expected calculation
==========================================
July 1, 2011          5
July 2, 2011         10

July 1, 2012          5
July 2, 2012         10

July 1, 2013          5                          5
July 2, 2013         10                        10```

The expected computation is ([July 1, 2011] + [July 1, 2012] + [July 1, 2013])/3.

Thanks,

Howard

Super User

Hi,

Frequent Visitor

Super User

## Re: Average of 3 Months

Hi,

This measure should work

Average on same day in previous 3 years = AVERAGEX(SUMMARIZE(VALUES(DimDate[Date]),[Date],"ABCD",SUM(FactInternetSales[SalesAmount]),"EFGH",CALCULATE(SUM(FactInternetSales[SalesAmount]),DATESBETWEEN(DimDate[Date],EDATE(MIN(DimDate[Date]),-1),EDATE(MIN(DimDate[Date]),-1))),"IJKL",CALCULATE(SUM(FactInternetSales[SalesAmount]),DATESBETWEEN(DimDate[Date],EDATE(MIN(DimDate[Date]),-2),EDATE(MIN(DimDate[Date]),-2)))),[ABCD]+[EFGH]+[IJKL])

When i drag this measure to the visual, i receive a message saying that not enough memory is available.  Try this on a smaller dataset.

Frequent Visitor

## Re: Average of 3 Months

Thanks, Ashish. I really appreciate your help.

Howard

Super User

## Re: Average of 3 Months

Hi,

Did my solution work?