cancel
Showing results for
Did you mean:
Highlighted
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

## Re: Rolling last months / Weeks

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.

2 REPLIES 2
Highlighted
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],
),
CALCULATE(SUM('Table (9)'[Month]))
)
)``````

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

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

Proud to be a Super User!

Highlighted
Microsoft

## Re: Rolling last months / Weeks

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### 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