Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Avg 5 weeks except certain week

I have a measure that averages the last 5 weeks but I now need to exclude a week in my calculation and have it average the last 5 weeks dynamically . The week number is user driven.

i.e. user wants to exclude week 29 because it's too high, it should get the average of week 24 through 28 which is 1,502. 

week 30 should be the average of week 25, 26, 27, 28, 30

 

wk no   units   5 wk avg   goal - 5 wk avg exclude wk 29
221,8641,601 
231,5281,625 
241,4621,642 
251,3701,634 
261,8491,6151,615
271,4971,5411,541
281,3301,5021,502
299,5863,1261,502 (avg wk 24 - 28)
30912,8711,227 (avg wk 25, 26, 27, 28, 30)

 

 

5 week average:=
VAR avg_5wk =
CALCULATE (
AVERAGEX ( fact, fact[units] ),
DATESINPERIOD ( DimDate[pk_date], LASTDATE ( DimDate[pk_date] ), -34, DAY )
)
RETURN
avg_5wk

 

Something I tried but didn't work was taking the user's week number and relating it to the date table with a calculated column that flags a 1. CALCULATE ( [5 week average], DimDate[WeekExclusion] <> 1 ) ) but this only works for that one week. How can I make it dynamic so that the results are like the table above?

 

5 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    You could try

    5 week average :=
VAR avg_5wk =
    CALCULATE (
        AVERAGEX ( fact, fact[units] ),
        DATESINPERIOD ( DimDate[pk_date], LASTDATE ( DimDate[pk_date] ), -34, DAY ),
        DimDate[Week no] <> SELECTEDVALUE ( 'Slicer Table'[Week no] )
    )
RETURN
    avg_5wk
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi John - thanks for your input but the results are identical to the original measure. Although the issue could be that I can't use SELECTEDVALUE as the tool must be in excel but my understanding is SELECTEDVALUE can be replicated with other functions.

       

      5 week average :=
VAR avg_5wk =
    CALCULATE (
        AVERAGEX ( fact, fact[units] ),
        DATESINPERIOD ( DimDate[pk_date], LASTDATE ( DimDate[pk_date] ), -34, DAY ),
        DimDate[YYYYWK]
            <> IF (
                HASONEVALUE ( slicer_table[YYYYWK] ),
                VALUES ( slicer_table[YYYYWK] )
            )
    )
RETURN
    avg_5wk

       

      In case you may ask, the data types for [YYYYWW] (i.e. 202229) in my date and slicer table are INT.

      I've thought about this for some time and it's been quite a challenge to skip a week and still get a 5 week rolling average - let me know if I can clarify further.

      • johnt75's avatar
        johnt75
        Super User
        4 years ago

        Try

        5 week average =
VAR fullDates =
    DATESINPERIOD ( DimDate[pk_date], LASTDATE ( DimDate[pk_date] ), -34, DAY )
VAR slicerDates =
    CALCULATETABLE (
        VALUES ( DimDate[pk_date] ),
        TREATAS ( VALUES ( slicer_table[YYYWK] ), DimDate[YYYWK] )
    )
RETURN
    CALCULATE (
        AVERAGE ( fact[units] ),
        TREATAS ( EXCEPT ( fullDates, slicerDates ), DimDate[pk_date] )
    )
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

     

    Best Regards,
    Community Support Team _ kalyj