Forum Discussion
7-day Rolling Average help
Hello, I am struggling with replicating a 7-day rolling average in Power BI that I can easily do in Excel. I'm new to Power BI and am struggling with search terms so this is probably a simple question. When Googling a solution, 7-day rolling averages focus on sales or revenue, but my data set needs to filter out a certain type of product first.
The Data:
I have a data set of products inspected daily with two dispositions: "Scrap" and "Test." I need to know the 7-day rolling average of the products dispositioned as "Scrap." In Excel, this is simply the sum of products dispositioned as "Scrap" divided by the sum of products inspected in the past 7 days. I've created this in the linked Excel file under the "RollingAverageTable" tab.
The Question:
When I try to create this in Power BI, it's less straightforward (pbi file above). I created a measure for Daily Total Count, but I'm not sure how to turn that into a 7-day rolling average of "Scrap" products only. I assume I need a separate measure to count the daily total of products dispositioned to scrap and then divide that by the daily total, but how would I go about doing that?
6 Replies
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,
In counting 6 days prior, you have ignored days on which there is no scrap count. Shouldn't those days be incouded with a value of 0 i.e. as on Feb 13, 2022, shouldn't 6 prior days be Feb 7 - 12?
