Weekly attendance, average and week on week change

[Anonymous]

I have data for a weekly event, held on the same day each week.  My data has a Date, Location & Count field which represents the count of people in attendance for that week/date for a particular location, and the data spans accross 2 years.

I have added columns for year, month, monthname & weeknum.

1. I want to see average attendance per week for each location.
2. I want to see weekly attendance change week by week

Please help.

[Anonymous]

Thank you @v-yanjiang-msft that looks pretty clear and straight forward.  

What's the difference between finding an average using your measure and simply dropping the Count column in a visual and selecting to summarise it by Average?

Also I know there are always many ways to do what I need  so I welcome someone else to provide another approach, for my reference.

[v-yanjiang-msft]

Hi @Anonymous ,

My pleasure!

If your data is just the same with my sample, there's no difference. The formula can ensure not be affected by slicer etc.

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, I create a sample and add a weeknum column. Date column are saturdays from each week.

Here's my solution.

1.Average attendance per week for each location. Create a measure.

Average Count =
AVERAGEX (
    FILTER ( ALL ( 'Table' ), 'Table'[Location] = MAX ( 'Table'[Location] ) ),
    'Table'[Count]
)

Put Location and the measure in a visual, get the result.

2.Attendance change week by week. Create a calculated column.

Weekly Change =
VAR _Current =
    SUMX (
        FILTER ( 'Table', 'Table'[WeekNum] = EARLIER ( 'Table'[WeekNum] ) ),
        'Table'[Count]
    )
VAR _Next =
    SUMX (
        FILTER ( 'Table', 'Table'[WeekNum] = EARLIER ( 'Table'[WeekNum] ) + 1 ),
        'Table'[Count]
    )
RETURN
    DIVIDE ( _Next - _Current, _Current )

Get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi again, this is good and i have added this in, however each location needs to have their own week 1,2,3 etc, like this.

Date	location	count	weeknum
1/1/22	A	9	1
1/1/22	B	18	1
1/1/22	C	10	1
1/8/22	A	6	2
1/8/22	B	20	2
1/8/22	C	20	2
1/15/22	A	8	3
1/15/22	B	5	3
1/15/22	C	9	3
1/22/22	A	8	4
1/22/22	B	15	4
1/22/22	C	11	4
1/29/22	A	15	5
1/29/22	B	5	5
1/29/22	C	20	5

What adjustments are needed to work with this data structure?

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

Sorry for late back, you can modify the formula to:

Weekly Change =
VAR _Current =
    SUMX (
        FILTER (
            'Table',
            'Table'[WeekNum] = EARLIER ( 'Table'[WeekNum] )
                && 'Table'[Location] = EARLIER ( 'Table'[Location] )
        ),
        'Table'[Count]
    )
VAR _Next =
    SUMX (
        FILTER (
            'Table',
            'Table'[WeekNum]
                = EARLIER ( 'Table'[WeekNum] ) + 1
                && 'Table'[Location] = EARLIER ( 'Table'[Location] )
        ),
        'Table'[Count]
    )
RETURN
    DIVIDE ( _Next - _Current, _Current )

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.