Forum Discussion
7-day Rolling Average help
khat Should be easy to adapt this approach: Better Rolling Average - Microsoft Power BI Community
Better Rolling Average =
VAR __EndDate = MAX('Table'[Date])
VAR __7DaysAgo = __EndDate - 7
VAR __StartDate = DATE(YEAR(__7DaysAgo ), MONTH(__7DaysAgo ), 1)
VAR __Table =
SUMMARIZE(
FILTER(ALL('Table'),[Date]>=__StartDate && [Date]<=__EndDate), // add filter here
'Table'[Month],
"__Value",SUM('Table'[Value])
)
RETURN
AVERAGEX(__Table,[__Value])
Thank you. I followed the link and watched the video as well. Learned a lot but it's still a bit advanced for me. Below is my attempt at using that measure and replacing it with my own tables, which did not work. Any tips on where I went wrong?
Thanks again for the video, I appreciated your explanation of VAR, which was new to me.
khat What does "See details" say?
Hi Greg_Deckler
Here is the text (image below):
Error Message:
MdxScript(Model) (13, 28) Calculation error in measure 'DateInspected'[Better Rolling Average]: The function SUM cannot work with values of type String.
I chose the DateInspection(ProductDisposition.Type) table because that is what I need counted and it was what I thought most closely matched your example, should I have used the Daily Scrap Count measure instead?
Thank you again for your help
khat You either need to change your Type column to a number or use a different aggregator like COUNT. Your Type column is Text right now so you can't SUM a Text column.