Dynamic rolling average based on slicer selection

[alan7lp]

Hello!

I have the following measure which gives me the rolling average of average sales per week:

Dynamic Rolling AVG = 
VAR virtual_table =
    SUMMARIZE (
        FILTER (
            ALL ( 'CALENDAR' ),
            'CALENDAR'[Date] <= MAX ( 'SALES_SPI'[Date] )
                && 'CALENDAR'[Date]
                    >= MAX ( 'CALENDAR'[Date] ) -21
        ),
        'CALENDAR'[SPI WEEK],
        "Rolling AVG", [AVG SALES in PCS]
    )
RETURN
    AVERAGEX ( virtual_table, [Rolling AVG] )

As you can see, this particular measure is giving me the rolling average of the last 3 weeks (hence the -21)

What I would like to have is such measure that will give me a total rolling average based on the totality of weeks that my report has but if I select week 20,  week 30 and week 35 to have the rolling average between those selected weeks (in this case are 3 but if I select 2 then those 2 or if I select 9 weeks then between those 9 and so on)

CURRENT CALCULATION
	A	B	C	D
1	WEEK	AVG SALES in PCS	Dynamic Rolling AVG	Logic behind
2	201936	39,225	39,225	B2 / 1
3	201937	40,125	39,675	(B2 + B3) / 2
4	201938	40,339	39,896	(B2 + B3 + B4) / 3
5	201939	39,908	40,124	(B3 + B4 + B5) / 3
6	201940	39,156	39,801	(B4 + B5 + B6) / 3
7	201941	38,342	39,135	(B5 + B6 + B7) / 3
8	201942	39,352	38,950	(B6 + B7 + B8 ) / 3

WISHED CALCULATION
	A	B	C	D	E
1	WEEK	AVG SALES in PCS	Dynamic Rolling AVG	Logic behind	Desired Result
2	201936	39,225	39,225	SUM(B2)/1	39,225
3	201937	40,125	39,675	SUM(B2:B3)/2	39,675
4	201938	40,339	39,896	SUM(B2:B4)/3	39,896
5	201939	39,908	40,124	SUM(B2:B5)/4	39,899
6	201940	39,156	39,801	SUM(B2:B6)/5	39,751
7	201941	38,342	39,135	SUM(B2:B7)/6	39,516
8	201942	39,352	38,950	SUM(B2:B8)/7	39,492

BASED ON SELECTION IN SLICER EXAMPLE
	A	B	C	D	E
1	WEEK	AVG SALES in PCS	Dynamic Rolling AVG	Logic behind	Desired Result
2	201936	39,225	39,225	SUM(B2)/1	39,225
3	201938	40,339	39,896	SUM(B2:B3)/2	39,782
4	201941	38,342	39,135	SUM(B2:B4)/3	39,302
5	201942	39,352	38,950	SUM(B2:B5)/4	39,315

I hope it's clear enough to get some help about it.

Thank you!

Cheers.

[v-lionel-msft · Accepted solution]

Hi @alan7lp ,

Try the measures:

Cumulative = 
CALCULATE(
    SUM(Sheet5[AVG SALES in PCS]),
    FILTER(
        ALLSELECTED(sheet5),
        Sheet5[WEEK] <= MAX(Sheet5[WEEK])
    )
)

//If you don't have [index] column, you can add it in Edit Queries.
Result = 
[Cumulative]/MAX(Sheet5[Index])

Best regards,
Lionel Chen

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

[alan7lp]

Thanks @v-lionel-msft ,

Your solution definitely works. I had to tweek it a bit cause the data sample I provided was just that, a sample but my model is more complex therefore it wouldn't work however because of your solution I could manage it 🙂 

In the end my original formula changed to the following:

AVG Rolling Sales = 

VAR AVG Rolling = 
SUMMARIZE(
    FILTER(ALLSELECTED('CALENDAR'),
         'CALENDAR'[WEEK]<= MAX('CALENDAR'[WEEK])
    ),
    'CALENDAR'[WEEK], "Cumulative", [SALES AVG (pcs)]
)

return
    AVERAGEX(AVG Rolling, [Cumulative])

Have a nice day and thanks once again!