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

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.