Average of multiple sets with offset 1 week each

[rteruyas]

Hello guys, how you're all doing well!

I have a report using dimensional data. To keep it simple, my report displays tuple ([Mesure],[52 last weeks])
[52 last weeks] is a member created using relative time for my Date dimension.

I've been asked to show the average of last ten [52 weeks].
Trying to translate it a little bit, it would look like
average ([52 last weeks], [52 last weeks lag 1 week]. [52 last weeks lag 2 weeks]... [52 last weeks lag 10 weeks])

I see two options here and want to know which one is suggested
1. Create a new member using relative time for all the other 9 group of weeks in the cube
2. Try to create the calculation in the report using lag, ParallelPeriod and lastPeriods

If the choice is number 2, then I'd like a little help here:
- First attempt:
I tried using
lastPeriods(52, lag(lastChild([52 last weeks],1))
However, the offset doesn't seem to work for the first week. If my [52 last weeks] are 2017-01 to 2017-52, then my calculation doesn't go before 2017-01

- Second attempt
I tried using instead
lastPeriods(52, parallelPeriod([Date].[Working Calendar].[Week];1;[52 last weeks])
But again, it doesn't seem to go before 2017-01 to get the offset

Any ideas what I'd be missing

* If the best option is to create each member (since I'll have to add this same calculation in several reports), feel free to reply as well.
Thanks in advance!  ;D

[CognosPaul]

You a way to identify the last week. Once you have that it's a simple matter of:

average([Measure] within set lastPeriods(10,[Last week]))

Getting that [Last week] is the hard part. One way that I like to handle it is to have a flag in the date dimension. item(filter([Week Level],[Is last week flag]=1),0)

There are a few other ways you could do this. What is your datasource? You could duplicate the [52 last weeks] member and change the aggregate method to average. Then it would be a simple tuple again.