cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Rolling last months / Weeks

I have two tables ( A calendar table (4/4/5 logic), Sales table) I want to calculate the rolling average of last 3/6/12 Months and also last 13/26/52 weeks. For example, If I would calculate for April 2020 it would be March 2020 + Feb 2020 + Jan 2020 / 3 For First 3 Months of the date table ( Nov 2019, Dec 2019, Jan 2020 it would be 0 because I will not be having the last 3 months for those). Sample Output would be 

 

Month Sales Avg 3 Months
April 2020 10 13.3
March 2020 10 16.6
Feb 2020 20 13.3
Jan 2020 10 0
Dec 2019 20 0
Nov 2019 10 0

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Rolling last months / Weeks

Hi @Ethanhunt123 ,

 

Refer to:

Measure =
IF (
    MAX ( Sales[Date] ) <= MAX ( DimDate[Date] )
        && MAX ( Sales[Date] ) >= EDATE ( MAX ( DimDate[Date] ), -3 ),
    CALCULATE (
        AVERAGE ( Sales[Sales] ),
        FILTER (
            ALL ( Sales ),
            Sales[Date] <= MAX ( DimDate[Date] )
                && Sales[Date] >= EDATE ( MAX ( DimDate[Date] ), -3 )
        )
    )
)

Sample .pbix

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: Rolling last months / Weeks

@Ethanhunt123 See if these help:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Average/m-p/160720#M3

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499#M124

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694#M128

 

Also, there is a built-in Rolling Average Quick Measure in Power BI Desktop. Click the ellipses on a numeric field and then choose New Quick Measure | Rolling Average. It will produce something like:

Month rolling average = 
IF(
	ISFILTERED('Table (9)'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __LAST_DATE = LASTDATE('Table (9)'[Date].[Date])
	RETURN
		AVERAGEX(
			DATESBETWEEN(
				'Table (9)'[Date].[Date],
				DATEADD(__LAST_DATE, -1, DAY),
				DATEADD(__LAST_DATE, 1, DAY)
			),
			CALCULATE(SUM('Table (9)'[Month]))
		)
)

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Microsoft
Microsoft

Re: Rolling last months / Weeks

Hi @Ethanhunt123 ,

 

Refer to:

Measure =
IF (
    MAX ( Sales[Date] ) <= MAX ( DimDate[Date] )
        && MAX ( Sales[Date] ) >= EDATE ( MAX ( DimDate[Date] ), -3 ),
    CALCULATE (
        AVERAGE ( Sales[Sales] ),
        FILTER (
            ALL ( Sales ),
            Sales[Date] <= MAX ( DimDate[Date] )
                && Sales[Date] >= EDATE ( MAX ( DimDate[Date] ), -3 )
        )
    )
)

Sample .pbix

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors