topic Re: Avg 5 weeks except certain week in DAX Commands and Tips

Avg 5 weeks except certain week

Anonymous — Thu, 28 Jul 2022 20:01:36 GMT

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
22	1,864	1,601
23	1,528	1,625
24	1,462	1,642
25	1,370	1,634
26	1,849	1,615	1,615
27	1,497	1,541	1,541
28	1,330	1,502	1,502
29	9,586	3,126	1,502 (avg wk 24 - 28)
30	91	2,871	1,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?

Re: Avg 5 weeks except certain week

johnt75 — Fri, 29 Jul 2022 08:53:33 GMT

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

Re: Avg 5 weeks except certain week

Anonymous — Fri, 29 Jul 2022 14:30:17 GMT

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.

Re: Avg 5 weeks except certain week

johnt75 — Fri, 29 Jul 2022 14:40:27 GMT

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] ) )

Re: Avg 5 weeks except certain week

Anonymous — Fri, 29 Jul 2022 17:15:17 GMT

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.

Re: Avg 5 weeks except certain week

v-yanjiang-msft — Thu, 04 Aug 2022 09:09:18 GMT

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