Power Platform Community Forum Thread Details

I have a SharePoint list with two important columns: WeekRating (number) and Answer Date (Date)

I am struggling to work out how the Average method works

I want to get the average of the WeekRatings that reside inside each week block. For example: I have this working for the weeks but I'm not sure how to get the rest of this working:

Clear(RangeWeeks);

ForAll(Sequence(1 + DateDiff(MonDate1, MonDate2, TimeUnit.Days) / 7),

Collect(RangeWeeks,
{
WeekStart: DateAdd(MonDate1, (Value - 1) * 7, TimeUnit.Days),
Score: Average(ShowColumns(Filter('Form Answers', AnswerDate >= DateAdd(MonDate1, (Value - 1) * 7, TimeUnit.Days) And AnswerDate <= DateAdd(DateAdd(MonDate1, (Value - 1) * 7, TimeUnit.Days), 6, TimeUnit.Days)), WeekRating))
}))

Categories:

Connectors

General Questions

Using Apps Using Formulas

Your formula needs to be cleaned up quite a bit. For one thing you're using the Average() function without giving it a column to average. The way it's written you're just telling it to Show the WeekRating Column. You can see this if you format your formula a bit:

If you want to average "WeekRating" you need to put it in again as the second argument for the Average() function.

e.g.

That said your filters aren't delegable and won't work on large lists.

I'm not 100% sure what you're going for, but using my best judgement I'd do something like this using two date pickers and a nested gallery:

The formula for the gallery would be something like:

With({
    MonDate1: DatePickerCanvas1.SelectedDate, 
    MonDate2: DatePickerCanvas1_1.SelectedDate
    },
AddColumns(
GroupBy(
AddColumns(
    Filter(
        'Form Answers Demo',
        AnswerDate >=MonDate1 And AnswerDate <= MonDate2
    ), 
    Week, RoundUp(DateDiff(MonDate1,AnswerDate,TimeUnit.Days)/7,0)
),Week,'Forms For Week'
),'Week Score Avg',RoundUp(Average('Forms For Week',WeekRating),2)
))