Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic rolling average based on slicer selection

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
 ABCD
1WEEKAVG SALES in PCSDynamic Rolling AVGLogic behind
220193639,22539,225B2 / 1
320193740,12539,675(B2 + B3) / 2
420193840,33939,896(B2 + B3 + B4) / 3
520193939,90840,124(B3 + B4 + B5) / 3
620194039,15639,801(B4 + B5 + B6) / 3
720194138,34239,135(B5 + B6 + B7) / 3
820194239,35238,950(B6 + B7 + B8 ) / 3

 

WISHED CALCULATION
 ABCDE
1WEEKAVG SALES in PCSDynamic Rolling AVGLogic behindDesired Result
220193639,22539,225SUM(B2)/1             39,225
320193740,12539,675SUM(B2:B3)/2             39,675
420193840,33939,896SUM(B2:B4)/3             39,896
520193939,90840,124SUM(B2:B5)/4             39,899
620194039,15639,801SUM(B2:B6)/5             39,751
720194138,34239,135SUM(B2:B7)/6             39,516
820194239,35238,950SUM(B2:B8)/7             39,492

 

BASED ON SELECTION IN SLICER EXAMPLE
 ABCDE
1WEEKAVG SALES in PCSDynamic Rolling AVGLogic behindDesired Result
220193639,22539,225SUM(B2)/1             39,225
320193840,33939,896SUM(B2:B3)/2             39,782
420194138,34239,135SUM(B2:B4)/3             39,302
520194239,35238,950SUM(B2:B5)/4             39,315

 

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

 

Thank you!

Cheers.

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    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.

2 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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!