Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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 )
)
)
)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
@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]))
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |