Forum Discussion

Ethanhunt123's avatar
Ethanhunt123
Icon for Helper IV rankHelper IV
6 years ago
Solved

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 Apri...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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