Rolling last months / Weeks

[Ethanhunt123]

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

[V-lianl-msft · Accepted solution]

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.

[Greg_Deckler]

@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]))
		)
)