cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mikemagill Frequent Visitor
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
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]
    )
)

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




5 REPLIES 5
Super User
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

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

Hi,

 

Share a dataset and show the expected result.

mikemagill Frequent Visitor
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
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]
    )
)

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




mikemagill Frequent Visitor
Frequent Visitor

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

Thank you so much,  That works perfectly - I just need to do some homework to understand what's happening in the formula (although your comments help greatly).