Presenting average-to-date in table

[CloudMonkey]

Hi,

I have sales data mapped by region and week. I can create a table of sales by week (columns B to H below) but please can you tell me how to create the rolling to date average for the trial period (columns I to O) (i.e. column K is the average of the first 3 weeks, column O is the average of the first 7 weeks). The table will need to expand automatically as data is added for new weeks.

Thanks,

CM

[v-sihou-msft · Accepted solution]

@CloudMonkey

In this scenario, you can create an "Average to Date" measure. But this measure must be along with each week sales, which means it can only appear within each column group. We can't have all measure results appear in another matrix next to the initial matrix. 

Average to Date =
CALCULATE (
    AVERAGE ( Table5[Sales] ),
    FILTER (
        ALL ( Table5 ),
        Table5[Week] <= MAX ( Table5[Week] )
            && Table5[Region] = MAX ( Table5[Region] )
    )
)

Regards,

[CloudMonkey]

Hi, I'm assuming from the lack of response this isn't possible? Or was I unclear in the description?

Thanks,

CM