cancel
Showing results for
Did you mean:
Frequent Visitor

## Rolling Average - attendance per week with variable number of events

I have a table of attendance counts for different events.  Some weeks there are two events, other weeks there is just one event and occasionally there are no events.  I want to calculate the 52 week rolling average attendance per week.

I tried this measure but it calculates the average attendance per event not per week.

CALCULATE(AVERAGE('Total Attendance'[Count]),DATESBETWEEN('Date Table'[Date],LASTDATE('Date Table'[Date])-364,LASTDATE('Date Table'[Date])))

How do I aggregate the event counts per week before averaging them over the number of weeks where there is at least one count?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Rolling Average - attendance per week with variable number of events

If you want to calculate how many people visited (as many) events (as they are) per week and then rolling Average them only over weeks with events, this formula should deliver the desired result:

```AvgAtt = IF (
ISBLANK ( SUM ( 'Total Attendance'[Count] ) );  // 4) Return value only if there has been an event
BLANK ();
AVERAGEX (                                      // 3) Average the weekly figures
SUMMARIZE (                                 // 2) Group by week and sum all attendees
CALCULATETABLE (                        // 1) Get relevant period
'Total Attendance';
DATESBETWEEN (
'Date Table'[Date];
LASTDATE ( 'Date Table'[Date] ) - 364;
LASTDATE ( 'Date Table'[Date] )
)
);
'Date Table'[Week];
"WeeklySum"; SUM ( 'Total Attendance'[Count] )
);
[WeeklySum]
)
)```

Proud to be a Datanaut!

Imke Feldmann

5 REPLIES 5
Super User

## Re: Rolling Average - attendance per week with variable number of events

Hi @mikemagill,

First of all your informationis not very clear about the dataset and the way you are calculating the count of the events, however if I can understand correctly you are counting the events and then want thhe average. You need to setup a AVERAGEX function in order for it to work.

Should look something like this:

```Average Of Events =
CALCULATE (
AVERAGEX ( Table, [Count] ),
DATESBETWEEN (
'Date Table'[Date],
LASTDATE ( 'Date Table'[Date] ) - 364,
LASTDATE ( 'Date Table'[Date] )
)
)```

Not really sure if this works since this as to be adjusted to your dateset however this is the general  formula.

Can you share some data sample or a copy of the PBIX file?

Regards,

MFelix

Proud to be a Datanaut!

Super User

## Re: Rolling Average - attendance per week with variable number of events

Hi,

Share a dataset and show the expected result.

Frequent Visitor

## Re: Rolling Average - attendance per week with variable number of events

This a very simple subset of the data that shows attendance data over a five week period (1-7, 8-14, 15-21, 22-28 January, 29 January - 4 February):

Date, event type, count

01 January 2018, AM, 100

01 January 2018, PM, 110

08 January 2018, AM, 120

24 January 2018, PM, 130

31 January 2018, PM, 70

01 February 2018, AM, 80

I want to calculate the average attendance per week where there are any events.  In this example, the average over the period is 152.5 (i.e. 610/4).

Does that help?

Super User

## Re: Rolling Average - attendance per week with variable number of events

If you want to calculate how many people visited (as many) events (as they are) per week and then rolling Average them only over weeks with events, this formula should deliver the desired result:

```AvgAtt = IF (
ISBLANK ( SUM ( 'Total Attendance'[Count] ) );  // 4) Return value only if there has been an event
BLANK ();
AVERAGEX (                                      // 3) Average the weekly figures
SUMMARIZE (                                 // 2) Group by week and sum all attendees
CALCULATETABLE (                        // 1) Get relevant period
'Total Attendance';
DATESBETWEEN (
'Date Table'[Date];
LASTDATE ( 'Date Table'[Date] ) - 364;
LASTDATE ( 'Date Table'[Date] )
)
);
'Date Table'[Week];
"WeeklySum"; SUM ( 'Total Attendance'[Count] )
);
[WeeklySum]
)
)```