Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mikemagill
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
ImkeF
Super User
Super User

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]
    )
)

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

5 REPLIES 5
ImkeF
Super User
Super User

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]
    )
)

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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).

mikemagill
Frequent Visitor

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? 

Ashish_Mathur
Super User
Super User

Hi,

 

Share a dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.