Calculate a rolled up average (baseline) to compare against other weeks.

[AT1]

I am using Excel 2016 and need a PowerPivot solution for the following situation.

I have a table with 5 columns. The names (and data types) of each column: Name (text), WeekNumber (int), DatePosted (date), Weekend (date), Measure (int).
The WeekNumber column represent the week number of the corresponding DatePosted date.

Table:

Desired output:
I need a DAX formula to calculate the average of Measure in the table above for weeks 1-11 as a baseline and compare against the week 12 average, week 13 average and so on.

I am not certain how to capture the average for only weeks 1-11, as weeks 12, 13, 14 and so on are also in the dataset.

The numerical values in white are simple SUMs. I need to create a formula that I could use in a pivot table that represents the value(s) in Q (the average of week 1-11) for each Name.

Thank you.

 

[JustynaMK]

Hi,

I have used a simplified version of your dataset, but that should do the trick. As you can see, my average for weeks 1-11 is 67.44.

You can then create DAX measure to calculate average only for weeks 1-11:

Avg1-11 = 
    CALCULATE(
        AVERAGE(Table1[Measure]),
        FILTER(ALL(Table1), Table1[WeekNumber] <= 11)
    )

Here's the final result (table in PowerBI):

 

[AT1]

Thanks, that is very close to the result I am looking for. Is there a way to show in the Avg1-11 column the result by Name? So, for each Jim row it would show 59 in Avg1-11 column and Lewis would show 85?

 

[JustynaMK]

Sure
ALLEXCEPT will ignore all filters except for the [Name] column.

Avg1-11 =
    CALCULATE(
        AVERAGE(Table1[Measure]),
        FILTER(ALLEXCEPT(Table1, Table1[Name]), Table1[WeekNumber] <= 11)
    )

 

[AT1]

Yes this also works perfectly. Lastly (thank you for your patience), what if I want to sum the weeks (middle section in the image) for Jim and Lewis before coming up with the average, and then displaying the baseline Avg1-11 in column Q? I tried to alter your last formula using SUM but no luck.

 

[JustynaMK]

Hey,

I think you are looking for this formula - give it a try:

Sum of WeekNumber =
    CALCULATE(
        SUM(Table1[Measure]),
        ALLEXCEPT(Table1, Table1[Name], Table1[WeekNumber]))