Get average of measure for a set of dates

[rteruyas]

I have a cube with the following dimensions

1. Date
    Year
    Month
    Week
    Day

2. Product

3. Measures
    Qty Sold

I need to get Average of last 5 days, and then start iterating it 20 times
example (today as of March 8th)

Column1: Avg1: Avg(Qty Sold) from March 4th to March 8th
Column2: Avg2: Avg (Qty Sold) from March 3th to March 7th
Column3: Avg3: Avg (Qty Sold) from March 2nd to March 6th
Column4: Avg4: Avg (Qty Sold) from March 1st to March 5th
....
Column20: Avg20

My idea was this:
1. Create a column for have the 20 set of days identified
             Period1 = filter([Day],[Date-Key] between 20170308 and 20170304) and so on....

2. Create a tuple for this set (this is where I fail)
            Avg1 =  Total([Qty Sold] within set set([Period1])

I was thinking a combination of tuple and filter/set could do the trick, but all my attempts have failed.
Could you please guide me to have these calculations made.
Thanks in advance

[rockytopmark]

This is best done by adding a column to your Date Dimension table that represents which 5 day set the date belongs to, then add a level using that to your existing hierarchy, or perhaps add an alternate hierarchy that uses that new level.

Trying to do this in the report will probably hurt performance.

That said... to do this in the report, try creating data items for each 5 day set (Set1, Set2, Set3, ... ), using the lastPeriods() and except() functions in tandem

simplified example data items, and their expressions:
currentDate: filter([Sales (analysis)].[Time].[Time].[Day], [Sales (analysis)].[Time].[Time].[Day].[Day key] =  '20170308')
  ... this is obviously hard-coded for this example... you would want the current date value in the filter to be derived dynamically
Set1... includes the current date:  lastPeriods(5,item([currentDate],0))
Set2... The 5 days prior to Set1: except(lastPeriods(10,item([currentDate],0)),lastPeriods(5,item([currentDate],0)))
Set3... The 5 days prior to Set2: except(lastPeriods(15,item([currentDate],0)),lastPeriods(10,item([currentDate],0)))

Lastly, wrap each of the sets with: average(currentMeasure within set [Setn]) to get your desired column data items

Example attached shows the individual dates in each set, with that sets Avg below the last date of the set. I am using 2013-05-18 as the current date, since it is written using Go Sales (Analysis) package.

Your desired layout is probably different