Forum Discussion
Helper IVRolling 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 |
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 ) ) ) )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
- Greg_Deckler
Community Champion
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])) ) ) 
V-lianl-msftCommunity Support
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 ) ) ) )Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
