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 ...
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] )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I got frustrated with the lack of functions in power pivot and put the whole thing in power bi to see if I can get it to work.

      Tried the first measure again with SELECTEDVALUE but it's the same as my original measure.

      Your second suggestion with TREATAS appears to be doing the same as SUM ( fact[units] ) when filtered on one sku but it seems to divide that by the number of skus I select. If week 30 is 100 and I have 5 skus filtered, it's 20.

      Also, this is may be nothing but I noticed that TREATAS wasn't being picked up by the intellisense even though your measure is valid.