Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Howard_Hong
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
Ashish_Mathur
Super User
Super User

Hi,

 

Share some data and also show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

 

 

 

 

 

 

 

 

 

 

 

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks, Ashish. I really appreciate your help.

 

Howard

Hi,

 

Did my solution work?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.