cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Howard_Hong Frequent Visitor
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
Super User

Re: Average of 3 Months

Hi,

 

Share some data and also show the expected result.

Howard_Hong Frequent Visitor
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
Super User

Re: Average of 3 Months

Hi,

 

Share the link from where i can download your PBI file.

Howard_Hong Frequent Visitor
Frequent Visitor

Re: Average of 3 Months

Super User
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.

Howard_Hong Frequent Visitor
Frequent Visitor

Re: Average of 3 Months

Thanks, Ashish. I really appreciate your help.

 

Howard

Super User
Super User

Re: Average of 3 Months

Hi,

 

Did my solution work?